Using a C++ DLL in a Microsoft Office VBA Project

The first programming language I learned was VBA, the language built into Microsoft Office. Most actuarial work involves building complex financial models in Microsoft Excel, and building complex models is a whole lot easier if you have a well-designed programming language available.When I was working as an actuary, VBA it was the only programming language I needed. It was easy to use, easy to explain to others and it got the job done.

On a side note, if you are a young actuary, you really need to read this book on VBA modeling.

When I ran off to graduate school, I began working on very large math problems. Many of these math problems were so complicated that I needed to work on them in a "real" programming language. I began learning C++, one of the most powerful programming languages available.

At some point, I realized it would be cool if I could call C++ code directly from Excel. The reason I want to do this is so I can use CUDA to offload some of my Excel calculations onto the GPUs in my computer. I'll save the details for another post, and use this post to create a C++ DLL (also called a Class Library) and then call a function in that class library from VBA.

So here it is, step by step:

1. Open Visual Studio and create a new C++ Class Library project.

2. Create a header file and an implementation file using the C++ language. If you've never used C++ before, a good book for beginners is Sams Teach Yourself C++.

My header file, named DLL_for_Excel.h, has only one line of code:

int MyNum(void);

My implementation file, DLL_for_Excel.cpp, is also short:

#include "stdafx.h"
#include "DLL_for_Excel.h"
int MyNum(void) { return 123;}

Basically, all this program consists of a function called MyNum that returns the number 123.

3. Since you're building a .DLL file, you also need to create a definition file. This tells the computer what functions are available within the .DLL file. My definition file,DLL_for_Excel.def, has three lines of code:

LIBRARY "DLL_for_Excel"
EXPORTS
MyNum @1

All this is doing is telling the computer that the .DLL file contains a function called MyNum.

4. At this point, you can debug and compile the .DLL file. Copy the .DLL file into a filder where the computer "can see it" (i.e. any folder that is listed in the PATH environmental variable).

5. Now you are ready to use the .DLL file in an Excel VBA project. This is done using the declare function in VBA (it goes at the top of the VBA module). Here is the template for the declare function:

[Public Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]

And here is the declare function I used to access the sample .DLL I just created:

Public Declare Function MyNum Lib "DLL_for_Excel.dll" () As Integer

6. The once you have declared the C++ function, you can use it as if it were a VBA functions. Here is an example:

Public Sub mytest()
MsgBox MyNum()
End Sub

These three lines of VBA code will create a message box that shows the number 123.

That's really all there is too using a C++ Function in VBA.