Using C++ and VBA in Excel

One of the most powerful features in Microsoft Excel is the ability to use VBA to code modules and subroutines which can be used together with the spreadsheet. However, if you are someone who is more comfortable with C/C++, you might find VBA cumbersome. This post will describe to you how you can use C++ to create DLL which can be used with Excel with minimum VBA coding. Note that we are not using any class, we are just using the primitive ways to create a C++ function.

First create you need to decide the environment you wish to develop your C++ code. In this case, I start off by using Microsoft Visual Studio 2012.

Step 1.
Create a empty Win32 Visual C++ project and you will get to the Wizard page.

Remember to check the "DLL" radio button and "Empty Project".

Step 2.

Create a source file under the folder "Source" where you will write all your C++ codes.

For example, I created the addition function as follows:

/* Author: Ang Kian Liong

*/

double __stdcall addition(double & x, double & y){

return x+y;

}

Note that this function uses "__stdcall " which essentially tells the compiler to compile the codes in a certain way such that VBA can understand. For the more advance reader you can go to stackexchange or Wikipedia for more information on how it works technically. 

Step 3.

Now what you have done is define and describe a function in C++ that can be compiled in . However, in order for an external party to use the DLL, it needs to know what functions are there in the library and how it can access it. Therefore, you need to create a definition file as follows:

LIBRARY "ADD"

EXPORTS

addition

Save as a ".def" file in the same folder as your C++ source files. Access your linker properties, Project-><<Project name>> properties. Add the definition file under "Module Definition File".

Resolve any syntax error on your code and compile your code.

Step 4.

Next, we will explore how this can be invoked in Excel VBA. Open Excel , create a new Macro Enabled Excel Spreadsheet and press "ALT + F11" to get into VBA development environment. Insert a new module, in this case I name it "Module 1". To use the DLL we created earlier we need to declare in VBA the function that is we want to use from the DLL. We also need to specify the location of this DLL. For example,

Declare Function addition _

Lib "C:\Users\Ken\Documents\Visual Studio 2012\Projects\Win32Project2\Debug\Win32Project2.dll" (ByRef x As Double, ByRef y As Double) As Double

Now try to use the "addition" function in Excel and you will find that you can use it just like any other functions that come with Excel.

Enjoy! 

Here is the compiled DLL with Excel.

Note: 

1) If you are using the DLL in Excel, you will not be able to change and re-compile your code in C++ because ur Excel has already open and is using the DLL. You will get an error saying you cannot open the DLL file for overwriting. You will need to close your excel and compile the code before opening Excel again.

2)  Remember to enable macro when you open up the Excel Spreadsheet.

3) For those who tried to code C++ function without using passing by reference, you will notice that it will not work. I believe it is because Excel is using passing by reference by default and thus require all DLL to be written in the same fashion.