Runtime loading of DLLs

[MSINL]

Hi guys,
My problem is more like a VB problem, than a VBA problem.

I have a DLL that is to be used by the macros in excel. Uptill now i was happy by declaring the functions of the DLL as:

Please Login or Register  to view this content.

Now the problem is that the path of this DLL would be known at runtime. I can no longer use hardcoded paths.

Can anybody help with, how to load the DLL in VBA at runtime?

I tried adding the path of the DLL to the "PATH" environment variable. The SetEnvironmentVariableA (in kernel32.dll) returns TRUE, but if i try to read the variable again (using Environ), i don't find my modifications.

Please help.

[Leith Ross]

Hello MSINL,

Firstly, have you registered your DLL? It is possible Excel requires this DLL to be on the search path. In this case you could move it to \Windows\System32 or it may need to be in the application directory.

To register it, you need to move to the directory where the DLL is located and use the commandline tool RegSvr32. This tool should be on your search path in \Windows\System32.

To Register the DLL
1. Click Start > Run...
2. Type in the Regsvr32.exe MyLibrary.dll Change MyLibrary.dll to the name of your dll.

Add a Project Reference to the VBE

1. Open your workbook and then press ALT+F11 to open the VBE.
2. Press ALT+T followed by the Enter key.
3. Scroll down the list until you find Microsoft Visual Basic for Applications Extensibility x.x Object Library.
4. Press the Space bar to select it, and press Enter
5. Press CTRL+S to save the reference in the project

Once the DLL has been registered, a reference to it can be made at runtime by using the Global Universial Identifier or GUID for short. This is also referred to as a Class ID or CLSID. If you don't know the GUID for your dll then you have to use Find in the Registry.

Finding and Copying the GUID for your DLL
1. Click Start > Run...
2. Type in regedit.exe and press Enter
3. Once the Registry Editor opens, press CTRL+F
4. Type in the name of your DLL
5. Make sure the check box at the bottom labeled Match only string only is not checked.
6. The Search the results for TypeLib keys.
7.Click the key to open it.
8. On the right, you will se a window that lists the Name, Type and Data.
9. Click Name
10. To Copy the GUID, press ALT+E, press M, press CTRL+C
11. To close the Registry Editor, press ALT+F4

Macro to Create Runtime Reference to the DLL

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[MSINL]

Hi Ross,

Thanks for the detailed explanation.

Was just wondering, how do i call the functions exported by the DLL. Do we need to have something like a GetProcAddress to get the address of the function to call it?

Also, if the workbook is to move across machines, the DLL may not be registered on all of them, or won't have the same GUID (so probably can't hardcode it in the code).

But thanks a lot for providing solution on dynamic loading of the DLL.

[Leith Ross]

Hello MSINL,

Once the DLL has been registered and loaded, the system will able to locate it. You access the the functions using Declare Function statements.

Each machine will have to register the DLL if it is to access it locally. Once a GUID has been created, it can be used on any machine. The chances a GUID will ever repeat are astronomical.

Sincerely,
Leith Ross