Accessing and calling DLLs from VBA (Visual Basic for Applications)

These are some findings when I experimented with calling a DLL from Visual Basic for Applications.

I created the DLL from C sources.

Generally, functions that are exposed by the DLL to be called from VBA need to be declared with the__stdcall attribute (calling ).

In the VBA source code, the functions to be called in the DLL are made known to VBA with the declare statement.

The first test was to pass values byVal and byRef.

In the next test, I tried to pass null pointers to the DLL.

This test tried to pass pointers to structs (type).

This example tries to explore various ways to return a string from a DLL.

It is even possible to return an array of strings from the DLL which is demonstrated in this test.

In this example, I demonstrate how a DLL can call a function that is declared in the VBA code.

This example tries to pass a VBA object to a DLL and have the DLL invoke a member method on the class.

Compiling the sources

The sources for these tests are in my Github project VBA-calls-DLL.

All C sources are named dll.c and located in their own subdirectory. The subdirectories also come with a *.bas file that then uses the compiled DLL.

GNU compiler

With the GNU compiler (MinGW), I created the DLLs in cmd.exe like so:

gcc -Wall -Werror -c dll.c
gcc -shared dll.o -o the.dll -Wl,--add-stdcall-alias

The --add-stdcall-alias linker option is needed to undecorate the exported function names.

TODO: It is unclear what to do if the VBA code runs in a 32-bit environment but only a 64-bit MinGW compiler is available. Using the -m32 option of gcc resulted in a linker error.

Microsoft's (visual?) compiler

With Microsofts Compiler, I created the DLLs like so

cl /LD dll.c user32.lib /Fethe.dll /link /def:dll.def

I didn't find any option in cl to undecorate exported function names, therefore, I needed a dll.def file.

Referencing additional libraries

The returning a string example uses the SysAllocString WinAPI call which is implemented in oleaut32.dll. Thus, at least with the GNU compiler, oleaut32.lib needs to be linked as well:

gcc -shared dll.o -loleaut32 -o the.dll -Wl,--add-stdcall-alias

Specifying the path to the DLL

In order for the VBA run time environment to find the DLL, the directory path to the DLL needs to be either in the %PATH% environment variable or the full path to the dll needs to specified in the declare statement.

The directory path to the DLL can be added to the PATH variable in cmd.exe like so

C:\Path\do\directory\where\dll\was\compiled> PATH=%CD%;%PATH%

Excel can then be started with a variation of

C:\Path\do\directory\where\dll\was\compiled> "c:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"

TODO

This link contains some notes for developping DLLs for use with Visual Basic. It's a bit dated though (1996), and was written for VB 5.

Apparently, when strings are passed to DLL via the declare construct, the runtime of Visual Basic runtime does an automatic Unicode to Ansi and vice versa conversion. This seems to be the case even if the string is passed with an as any type.