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

The way I generally take care of this is by adding:

Dim CurrentPath As String
CurrentPath = CurDir()   
ChDir (ThisWorkbook.Path)

To: Private Sub Workbook_Open()

Answer 2

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.

1. Create a new module at runtime (you could import a .bas file from disk, no need to hard-code the module with string literals), using the VBIDE Extensibility API. Drawback: no compile-time validation; you'll need to use stringly-typed Application.Run calls to invoke it. Requires trusted programmatic access to the VBIDE API (i.e. you allow VBA to execute code that generates code that is then executed... like macro viruses do).
2. Use the LoadLibrary Win32 API... and now you've got pointers and addresses: this scary code (.zip download) is essentially a huge unmaintainable hack that uses assembly language to enable invoking the API functions by name. Looks like it only works for a subset of supported Win32 API functions though.
3. Change the DLL search path, but then that also requires dynamic code added at run-time, so might as well go with the above.

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!