VBA: how to set a dynamic library path for declare statement?

user_6044482 0
Viewed 1533

I have a program in Excel's VBA that need to call some methods from a DLL, both are under my control in term of code development. I need to be able to select what DLL library (from various releases) should be used at runtime, but the Declare statement from VBA only allow for constants in the Lib path.

The objective is multiple :

  1. Be able to select the library release at runtime from one Excel workbook
  2. Ease the code maintenance because a lot of library methods are used, meaning a lot of Declare statement to be edited when library path is changed
  3. Search dynamically at runtime the library from various predefined locations, to comply with deployment purposes. In dev environment, the library is in a dev directory, but for end-users, the library could be in 3 various locations: the workbook path, a specific directory on the computer, or a specific directory from a network drive.

I tried to declare the path in Public Const variable, but it doesn't works:

' This work
Private Declare PtrSafe Function addAmplifier Lib "path\to\lib.dll" (...) As Integer

' This don't work
Public Const libPath As String = "path\to\lib.dll"
Private Declare PtrSafe Function addAmplifier Lib libPath (...) As Integer

Is there a hijack way to use a variable in the Declare statement ? Or is there a way to create a VBA module able to edit the VBA code of the second module where the Declare statements are located in, and replace with regexp all occurrences of a String, save the VBA project then execute the module ? More formally : can VBA code rewrite itself at runtime ?

dynamic path vba excel dll
1 Answers

As suggested by L8n, the solution to change the working directory works fine.

So if the libraries are located in a sub-folder into the workbook path, we can do the following process each time a DLL native function is called :

  1. Change the working directory to go into the sub-folder with ChDir()
  2. Execute the native function
  3. Go back to the parent folder with ChDir()

However, I suspect the Frequent call of ChDir() could slow down the VBA execution since it also seems to update the execution context with some hidden variables from the VBA runtime.

user_60444820
Related