How To Load Win32 dlls Dynamically In VBA

Well, by dynamically i just mean that i don't know the path of the dll (or the path will be decided at runtime). now the problem is; you need to add a declares statement to use a function from VB but you don't know the path so there is no way in which you can give this statement unless the dll is going to be in the default dll search path. 

Now when i faced this question i only had a few things in my mind (i haven't tested (1) and (3) so please no rants if they don't work or mess something up)

1) Use "LoadLibrary" API. But the problem over here is it gives you the handle of the DLL then you can use "GetProcAddress " to get the address of the function you are interested in. I know ..i know its not that simple; but i am just over simplifying it because anyways you don't have a function pointer in VBA so you can't use the function even if you have the address. If you want to do it in VC have a look at Using classes exported from a DLL using LoadLibrary. Yes! i know there is a way to use it even from VB, but its looks so scary that i will not use it any application other then the once that only i am going to use, moreover using these sort of hacks workarounds means that you don't get any support when you are stuck!. If you are determined enough to use LoadLibrary from VB Here is the scary code.

2) Insert a module at the run time, this is something that i preferred when i faced this issue. The simple concept is to add a module at runtime containing the declares statement when you have the path information. Remember you will also need to add a dummy function dynamically which will call the real function that you want to call. Why ? because you can not include the real function in the code as it will generate a compile error. To call the dummy function you will need to use "Application.Run("DummyFunction") as you can not call it directly (Please .. don't ask me why !! because it will give a compile error as its not defined you will add it dynamically). below is the quick and dirty code that i used.

 Public Sub AddDynamic()
    Dim pth As String, ss As String
    pth = InputBox("Where is it ?")
    ss = "Public Declare Function Beep Lib """ & pth & _
    """ (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long" & vbCrLf & _
    "Sub DummyFunction()" & vbCrLf & _
        "Beep 1000, 1000" & vbCrLf & _
    "End Sub"
    Application.VBE.ActiveVBProject.VBComponents. _
    Add(vbext_ct_StdModule).CodeModule.AddFromString (ss)
    Application.Run "DummyFunction"
End Sub

3) Modify the dll search path  but again you will need to use a dummy function and you will need to add it dynamically ..so why not go the easier way (read (2))