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

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!

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

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

To: Private Sub Workbook_Open()

ChDir() should do the trick. It might not work on network folders though.

Declare Sub FortranCall Lib "Fcall.dll" (r1 As Long, ByVal num As String)...Dim CurrentPath As StringCurrentPath = CurDir()   ChDir (ThisWorkbook.Path)Call FortranCall(r, n)ChDir (CurrentPath) ' Change back to original directory

Now keep your .dll in the same folder as your workbook.