Programmatically set DLL search path in VBA macro

Frankly, I don't know what's the problem with using all those VBA code injection, assembly generation for LoadLibrary() calls, etc techniques that I've seen used for this simple task. In my project I use simple code to load dll from the same location as the workbook, like this:

Declare Function MyFunc Lib "MyDll.dll" (....) As ...Sub Test()  ....  ChDir ActiveWorkbook.Path  ... = MyFunc(....)End Sub

Excel 2003 at least, has no problem loading the dll from the current path, Set ChDir to whatever path your DLL has. You might also need to change your current drive which is separate from current path. You have to do it only once, before the first function call, after it the DLL stays attached no matter where your current path is, so you may do it once in workbook_open and not bother about the path later. I provide an empty dummy function in the DLL just for this pupose. I don't think MS Word is any different on this.

Private Declare Sub Dummy Lib "MyDLL.dll" ()Private Sub Workbook_Open()    ChDrive Left$(Me.Path, 1)    ChDir Me.Path    DummyEnd Sub

You can use LoadLibrary api.

For example in my projects the code looks like this:

If LibraryLoaded() Then   Call MyFunc ...End IfPublic 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 = IsLoadedEnd Function

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 CodeModuleDim vbc As VBComponentSet cm = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule).CodeModulecm.AddFromString (decString & funcString)cm.Name = "MyNewModule"Set vbc = cm.ParentApplication.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.