How can we use a string variable in a declare statement?

Question

A "normal" declare statement for a DLL function looks something like this:

 Declare Sub subName Lib "path_to_lib" _ Alias "aliasName" _ (...)

In my application, it would be nice to have the user select their library location, after which I write that location to a cell. I'd like to pass this value to the "path_to_lib" argument, but I'm having difficulty extracting the cell value.

I tried assigning the cell value to a global variable, say pathVariable and writing:

Declare Sub subName Lib " & pathVariable & " _ Alias "aliasName" _ (...)

But that returns the error: File Not Found: & pathVariable &

I also tried double quotes, which returned the error: File Not Found " & pathVariable & "

I then tried triple quotes, which VBA helpfully reduced to double quotes giving me the same error.

Is there some special syntactical sauce here; or even an alternative method? Or should I abandon this (helpful) feature?

Mathieu Guindon · Accepted Answer · 2018-09-11 19:23:27Z

2

You can't. Everything in the (declarations) section of a module can only ever be declarative statements, which aren't executable: a variable means nothing in a declarative context, it can't have a value. A Const could conceivably work though, but if you try it you get a compile error: VBA will only accept a string literal for it.

expected: string constant

answered Sep 11, 2018 at 19:23

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Erik A Over a year ago

Very true. The quotes are meant to delimit the string only, not to indicate it's a variable. This allows for paths containing parentheses, for example. Compiler constants also can't fill this gap.

Andres Salas Over a year ago

Interesting, thanks for the prompt reply. If only the user path didn't only work for spawned processes, it'd be nice to declare with just "nameOfDLL.dll" and use the input to add the folder location to the search path.

Mathieu Guindon Over a year ago

@AndresSalas barring VBA macro security settings, nothing forbids using the VBIDE API to generate code in a standard module at run-time, that includes the specific Declare statements you need to have; you could have procedure/function stubs at compile/design-time, and at run-time replace them with the appropriate Declare statements, and on teardown re-rewrite the module to remove the declares and put the stubs back in, in one go. If you go that route, make sure you save often - and be prepared to crash often before achieving a stable solution ...if something like it can work, anyway!

Erik A Over a year ago

I highly recommend against that. It requires a recompile and triggers a state loss, which is not something that should happen in finished applications. It can work, but proceed with a lot of caution and a lot of backups

Mathieu Guindon Over a year ago

@ErikvonAsmuth TBH anything that requires programmatic access to the VBIDE API shouldn't be production code in the first place. It's just the first "this might work" solution that crossed my mind. Interestingly that MSDN blog article is failing to address the fact that there's likely other code that needs to invoke that imported procedure/function, and that code will have to be compiled - and removing Option Explicit to make that happen is out of the question, at least as far as I'm concerned! Not sure what the ideal solution would be.

|

Andres Salas · Accepted Answer · 2018-09-11 21:24:43Z

I accepted Mathieu's answer since it gives a straightforward yes/no answer to my question.

However, for any users wondering how to get around this problem of dynamic dll locations, I have the following solution:

When a dll is called, the system first searches the current working directory and then searches the user and environment path variables. I found difficulty in modifying the path user variable for use in dll calls, so I exploited the first part by adding:

 ChDir (dllFolder)

Before the dll call. The declare statements can remain as they did before, with just the library name in quotes:

 Declare Sub subName Lib "DLLName.dll" _ Alias "aliasName" _ (...)

Feel free to keep track of the previous directory and change it back after the call if other parts of your program expect to be in a certain directory.

This should be the accepted answer, really. Nicely explained, +1!

Sancarn · Accepted Answer · 2021-04-15 17:09:17Z

Although you can't do declares in this way, you can at least load the library and function.

hDLL = LoadLibrary(myDLL) hProc = GetProcAddress(hDLL, "myProcName")

So now we have a pointer to the function. To call the function is a bit of a mess but you can use DispCallFunc to do this.

As said, this is a bit of a mess however LaVolpe on the vbforums made a neat class for this kind of thing

Private cMyDLL as cUniversalDLLCalls set cMyDLL = new cUniversalDLLCalls '... later ... cMyDLL.CallFunction_DLL(myDllPath, "myMethodName", hasStrinParams, returnType, CC_STDCALL, param1, param2, param3, ...)

Caveat:

This class is only supported on Windows OS

Collectives™ on Stack Overflow

How can we use a string variable in a declare statement?

3 Answers 3

9 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

Comments

Related