CODEWITHSUNDEEP
excelvba
Andres Salas
Andres Salas

Reputation: 175

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

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?

Upvotes: 1

Views: 830

Answers (3)

Sancarn
Sancarn

Reputation: 2866

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

Upvotes: 0

Andres Salas
Andres Salas

Reputation: 175

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.

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

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

Upvotes: 2

Related Questions