How to use dll's in the same directory as an excel file

Question

This is somewhat related to my other question.

I've been using a dll to acompany an excel spreadsheet. Everything is currently working with the dll and excel is using it just fine. But is it possible to specify that a dll resides in the same directory as the excel file when declaring functions?

Declare Sub FortranCall Lib "Fcall.dll" (r1 As Long, ByVal num As String)

Unfortunetly this doesn't work, I have to use something like:

Declare Sub FortranCall Lib "C:\temp\Fcall.dll" (r1 As Long, ByVal num As String)

This works, but is going to cause headaches when distributing to my office mates. Placing the dll in c:\windows\system32 etc. is not really an option either.

Answer 1

You can put the DLL in some directory and add it to the path EnVar.

Answer 2

ActiveWorkbook.Path gives you the full path to the folder containing the currently-active workbook. So try this:

Declare Sub FortranCall Lib ActiveWorkbook.Path & "\Fcall.dll" (r1 As Long, ByVal num As String)

Answer 3

Here are three possibilities for dynamically loading/calling into DLLs from VBA, including links to relevant info and some sample code. Can't say I've ever had to use any of the solutions described there, but it seems like a reasonable exploration of the options in light of VBA's need for a static path.

Here's another potential solution that suggests programmatically updating the PATH environment variable prior to calling into your DLL. Not a bad idea, if it works, as you could add this to you workbook open event.

Good luck!