Programmatically set DLL search path in VBA macro

Question

The problem

• I have a word template which uses VBA's Declare statement to link to a dll, whose path can be determined within the VBA macro
• I want to delploy this to the users %APPDATA%\Microsoft\Word\STARTUP directory
• I DON'T want to permanently change the user's PATH environment variable (temporarily would be OK, but this doesn't seem to work as they don't get refreshed until application restart)

Attempted solution

I tried dynamically adding the code with the Declare statements using ThisDocument.VBProject.CodeModule.AddFromString(code) which works when loading the template from a normal directory, but when the template is within Word\STARTUP, it gives the following error:

Run-time error '50289':

Can't perform operation since the project is protected.

And setting the registry key "HKEY___LOCAL_MACHINE\Software\Microsoft\Office\11.0\Word\Security\AccessVBOM" to 1 doesn't fix this when the template is in Word\STARTUP

---

I'm really struggling to find a solution. If anyone knows a way to do this, that would be great.

Answer 1

You can use LoadLibrary api.

For example in my projects the code looks like this:

If LibraryLoaded() Then Call MyFunc ... End If

Public Function LibraryLoaded() As Boolean

Static IsLoaded As Boolean
Static TriedToLoadAlready As Boolean

If TriedToLoadAlready Then
    LibraryLoaded = IsLoaded
    Exit Function
End If
Dim path As String
path = VBAProject.ThisWorkbook.path
path = Left(path, InStrRev(path, "\") - 1)
IsLoaded = LoadLibrary(path & "\bin\" & cLibraryName)
TriedToLoadAlready = True

LibraryLoaded = IsLoaded

End Function

Answer 2

There is another really really ugly solution, but this blogger figured it out, and I can't figure out any other way:

http://blogs.msdn.com/pranavwagh/archive/2006/08/30/How-To-Load-Win32-dlls-Dynamically-In-VBA.aspx

Basically, you write a procedure that creates a code module in VBA during runtime. This module must create a reference to the dll and it must create a dummy function (or procedure) as part of this module that calls the dll. Then, from your code, you use Application.Run(dummyfunction(), arg1, arg2...). This is necessary because otherwise, the project will not compile because dummyfunction isn't yet a function.

You'll notice in his code, he uses InputBox() to get the location of the .dll but obviously you could get the location from a range in the spreadsheet. The following code snippet may be useful.

Dim cm As CodeModule
Dim vbc As VBComponent

Set cm = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule
cm.AddFromString (decString & funcString)
cm.Name = "MyNewModule"
Set vbc = cm.Parent
Application.VBE.ActiveVBProject.VBComponents.Remove vbc

'decString' and 'funcString' were just strings I constructed like his 'ss'. The snippet shows how you can rename the code module so that you could delete it later if needed. Obviously, this just deletes it right after it is created, and you probably wouldn't want to do that, but at least it shows you how it would be done.

Having said all that, we mostly just write .exe's now and shell out. If you need VBA to wait on the shell to finish, there are solutions for that issue as well.