How to create a DLL for MS Excel VBA with Microsoft Visual C++ 2008 command line tools

Outline
In this post it is described in a step-by-step manner how to create a C/C++ dynamic linked library (DLL) for use with Visual Basic for Applications (VBA) in Microsoft Excel. Main emphasis is on using the DLL for scientific computations, thus it is necessary to be able to send arrays as input to the DLL and receive arrays as output. Only a very simple example will be given in order to illustrate this. It has been prioritized to show a working example rather than elaborating too much on C/C++ calling conventions, name decoration of exported function etc. For more information on these topics please consult the web (see e.g. the Further reading section below).

In order to follow the given example it is required to have a working installation of Microsoft Windows (example only tested on XP, but is assumed to work as well on Windows 2000 and Vista), including Microsoft Office (tested on Office 2000, but works as well with Office 2003 and 2007 I presume), and finally an installation of Microsoft Visual C++ 2008 (Express Edition). Visual C++ can be downloaded free of charge from Microsoft. If problems with the installation are experienced, seek assistance in the installation and setup forum.

Creating the DLL with Visual C++
Paste the code below into a text editor and save it as dllarray.cpp.

extern “C” int __declspec(dllexport) _stdcall myarray( double* pin, double* pout, int sz )
{
int i;
for (i = 0; i < sz ; i++)
{
pout[i]=pin[i]*100;
}
return 0;
}

The function “dllarray” takes two arrays as input (actually pointers to the adress of the first element of each array) along with the number of elements in the arrays (in this case the same). The function loops through the arrays and writes element i in pin times 100 to element i in pout (not really that scientific ;-). The return type of the function is an iteger (in this case 0), but error checking could be included as well with different return values. In order to compile the code open the Visual Studio 2008 Command Prompt

and change directory to the location of the file just created. Compile the source file with:

cl /c /LD dllarray.cpp and link with:

link /DLL dllarray.obj

This creates the DLL (dllarray.dll). In order to refer to the functions defined inside the dll it is necessary to extract the function names really exported (some name decoration has occured). This is achieved with the DUMPBIN tool shipped with Visual C++. On the VS2008 command line type:

dumpbin /EXPORTS dllarray.dll

This gives the following output:
Microsoft (R) COFF/PE Dumper Version 9.00.21022.08
Copyright (C) Microsoft Corporation. All rights reserved.

Dump of file myarray.dll

File Type: DLL
Section contains the following exports for myarray.dll
00000000 characteristics
481F8D73 time date stamp Tue May 06 00:42:59 2008
0.00 version
1 ordinal base
1 number of functions
1 number of names

ordinal hint RVA name
1 0 00001000 _myarray@12
Summary
2000 .data
2000 .rdata
1000 .reloc
A000 .text

From which it is inferred that the exported name of the function myarray is actually _myarray@12.

Calling the DLL from inside VBA in MS Excel
Open an empty workbook in Excel and enter the VBA editor (Alt+F11). Make a new module and paste the following code into it:

Declare Function myarray& Lib “C:\dllarray.dll” Alias “_myarray@12” _
(ByRef pin As Double, ByRef pout As Double, ByVal sz As Long)

Sub test_dll_array()
Dim sz As Long
Dim pin() As Double
Dim pout() As Double
sz = 10
ReDim pin(sz – 1), pout(sz – 1)
For i = 0 To (sz – 1)
pin(i) = i
Next i

y = myarray(pin(0), pout(0), sz)

MsgBox (“Value of 4. element in pin array : ” + Str(pin(3)))
MsgBox (“Value of 4. element in pout array : ” + Str(pout(3)))
End Sub

Run the code. This should pop up two message boxes, the first showing the value of the 4. element of the “input” array and the second showing the corresponding value written to the “output” array. The following conventions are very important when passing arrays to a C/C++ DLL

1. In the function declaration of the DLL the arrays should be passed “ByRef” (default) which passes the address of the first element rather than the value.
2. Integers (int) in C/C++ should passed as long’s from VBA.
3. The name following Declare Function is the name which is used in VBA (does not have to be the same as in the DLL), however the real (exported) name of the function in the DLL should be specified after the Alias (in this case with name decoration).
4. When calling the declared function the first element should be given, not the entire array.
5. When writing output to arrays in VBA from a C/C++ DLL it is by far the easiest approach to hand in the (empty) “output” arrays as input to the function. Using the pointer approach here the DLL can modify the arrays defined in VBA.

The above example with C++ can easily be modified to apply for ANSI C as well. Change the extension of the source file from *.cpp to *.c. Delete the extern “C” declaration and add the following option to the command line compiler /TC.

Further reading
http://support.microsoft.com/kb/207931
http://www.ozgrid.com/forum/showthread.php?t=63142
http://msdn.microsoft.com/en-us/library/dt232c9t%28VS.80%29.aspx