Linking Excel to C

One of the reasons often given for preferring programs like Matlab to Excel for scientific and engineering uses is the large body of specialist mathematical and scientific applications available to these programs.  In this and following posts I will describe how to compile programs written in C or C++ so that their functionality can be accessed from any Excel spreadsheet.  This will allow VBA programs in Excel to access such resources as:

• The GNU Scientific Library
• Numerical Recipes in C
• CR Bond’s maths and graphics routines
• The NetLib Repository

As well as these resources the use of compiled code offers much better security, and (under some circumstances) much better performance.

All the examples given in this and following posts have been compiled with Microsoft Visual C++ 2008 Express Edition, which is available for free download.  I have tried to keep the code as simple as possible, but the instructions will be easiest to follow and replicate if the same software package is used.

This post will cover:

• Compilation of C (or C++) code as a Windows API Dll, that can be accessed using other C programs.  This will follow a Microsoft tutorial at: Walkthrough: Creating and Using a Dynamic Link Library 
• Exporting the functions so that they are available from Excel.
• Declaring dll functions in VBA.

Later posts will describe:

• Accessing arrays created in C++ functions from VBA.
• Write and compile a simple C function to solve quadratic equations, and link to it from Excel.
• Compile a GNU Scientific Library (GSL) function to solve cubic equations, and link from Excel. 
• How to avoid the bottle-necks, and dramtically improve performance.

Walkthrough: Creating and Using a Dynamic Link Library 

 Following this walkthrough should produce code looking like this; a header file:

// MathFuncsDll.h
namespace MathFuncs
{
class MyMathFuncs
{
public:
// Returns a + b
static __declspec(dllexport) double Add(double a, double b);
// Returns a - b
static __declspec(dllexport) double Subtract(double a, double b);
// Returns a * b
static __declspec(dllexport) double Multiply(double a, double b);
// Returns a / b
// Throws DivideByZeroException if b is 0
static __declspec(dllexport) double Divide(double a, double b);
};
}

and a .cpp file:

// MathFuncsDll.cpp
// compile with: /EHsc /LD
#include "MathFuncsDll.h"
#include
using namespace std;
namespace MathFuncs
{
double MyMathFuncs::Add(double a, double b)
{
return a + b;
}
double MyMathFuncs::Subtract(double a, double b)
{
return a - b;
}
double MyMathFuncs::Multiply(double a, double b)
{
return a * b;
}
double MyMathFuncs::Divide(double a, double b)
{
if (b == 0)
{
throw new invalid_argument("b cannot be zero!");
}
return a / b;
}
}

 

Exporting the functions so that they are available from Excel.

The code produced by the MSDN walkthrough can be accessed from another C++ program, but will raise an error if access is attempted from Excel.  To make the functions available to Excel they must be “exported”, and we must also take care to ensure that the argument data types are compatible, and that parameters are passed consistantly (i.e. either by value or by reference) in both programs.  In addition there are some changes required to the project properties to allow the code to be de-bugged, and the code will be simplified, to focus on the main topics of this post.  The following steps describe the process, starting from scratch:

Open Visual C++ and select New-Project from the file menu.  Select Win32-Win32 Console Application from the New Project dialogue box, and enter a name for the Solution, such as WalkThrough2.  Click OK:

New Project 1

The Win32 Application Wizard will open, click Next>

New Project 2

Select Dll and Empty project, and click Finish.

New Project 3

 

At this stage the program will create a new folder for the project, with the name WalkThrough2 (or whatever name you have chosen), and open a “tree view” of the project files in the Solution Explorer window, with three blank sub-folders.

Right-click on the “Header Files” folder and select Add-New Item-Code-Header File, enter a name (MathFuncs2.h) and click add.

Right-click on the “Source Files” folder and select Add-New Item-Code-C++ file, enter a name (MathFuncs2.cpp) and click add.

Repeat to create a .def file, MathFuncs2.def

In these three files, copy or enter the code below:

// MathFuncs2.h
// Returns a + b
extern __declspec(dllexport) double Add(double a, double b);
// Returns a - b
extern __declspec(dllexport) double Subtract(double a, double b);
// Returns a * b
extern __declspec(dllexport) double Multiply(double a, double b);
// Returns a / b
// Throws DivideByZeroException if b is 0
extern __declspec(dllexport) double Divide(double a, double b);

// MathFuncs2.cpp
// compile with: /EHsc /LD
#include "MathFuncs2.h"
#include
using namespace std;
double Add(double a, double b)
{
return a + b;
}
double Subtract(double a, double b)
{
return a - b;
}
double Multiply(double a, double b)
{
return a * b;
}
double Divide(double a, double b)
{
if (b == 0)
{
throw new invalid_argument("b cannot be zero!");
}
return a / b;
}

; MathFuncs2.def - defines the exports for MathFuncs2.dll
LIBRARY MathFuncs2
EXPORTS
Add
Subtract
Multiply
Divide

At this stage the code entry is complete, and the project should look as shown below:

New Project 4

We now need to set the compilation options:
Click Project-WalkThrough2 Properties, and select the Configuration Properties-Debugging dialogue box. Enter or browse to the path to your Excel.exe file:

Properties 1

 

Select Configuration Properties-C/C++-Advanced. Select the __stdcall(/GZ) calling convention:

Properties 2

Select Configuration Properties-Linker-Input. Enter thename of the .def file under Module Definition File:

Properties 3

Setting the properties is now complete; click OK and select Build – Build Solution.  If everything has been enetered corectly the result should be:

….

========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
 

Compilation output

A new file MathFuncs2.dll wil have been created in a Debug folder under the solution folder (assuming the compiler has been left in the default debug mode).  To use these functions in Excel, in a standard code module in the Visual Basic Enter:

Declare Function Add Lib “D:\Users\Doug\Documents\Visual Studio Projects\WalkThrough2\Debug\MathFuncs2″ _
 (ByVal a As Double, ByVal b As Double) As Double
 Declare Function Subtract Lib “D:\Users\Doug\Documents\Visual Studio Projects\WalkThrough2\Debug\MathFuncs2″ _
 (ByVal a As Double, ByVal b As Double) As Double
 Declare Function Multiply Lib “D:\Users\Doug\Documents\Visual Studio Projects\WalkThrough2\Debug\MathFuncs2″ _
 (ByVal a As Double, ByVal b As Double) As Double
 Declare Function Divide Lib “D:\Users\Doug\Documents\Visual Studio Projects\WalkThrough2\Debug\MathFuncs2″ _
 (ByVal a As Double, ByVal b As Double) As Double

It will now be possible to enter the functions add(), subtract(), multiply() and divide() directly in the worksheet, or in any VBA routine.

The end result

The data files, dll file and spreadsheet may be downloaded from WalkThrough2.zip