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 <stdexcept>
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
Newton Excel Bach, not (just) an Excel Blog 
A great post!
I will be following this up when I have some time to play. Thanks for the step-by-step process in VC++, I am not familiar with VC++, but I am with VBA, Excel, and C++, so the navigation of the VC++ interface and all the settings in invaluable.
Cheers,
Rick
LikeLike
Pingback: Linking Excel to C « Newton Excel Bach, not (just) an Excel Blog
Pingback: Excel links of the week [Sep 8] | Pointy Haired Dilbert - Chandoo.org
If I take the files I’ve created and move them to another computer, they end up failing and giving a #VALUE! error in the excel cells. I thought initially that it could be that my functions were more complex so have also copied the functions in your zip file to another machine (ensuring I put the correct path in the VB in excel). Again, no luck.
I’ve since created a vb form with a button that runs an example and pops a messagebox with the result up. This can be debugged with the vb editor in excel and it gives me an Error 48, File Not Found. Is there anything that I could be missing on other machines that was installed as part of visual studi or do you have any other ideas.
Thanks for the great tutorial – came at just the right time 🙂
LikeLike
Tim – I don’t know immediately. I’ll do some tests on my machines, and see if I have the same problem (tomorrow; 11pm here now)
LikeLike
I think I’ve solved it – it requires the visual c++ 2008 redistributable files installed to work
LikeLike
Tim – yes that would be it. I’m only doing these things for my own use at the moment, so I hadn’t looked into deployment issues.
Microsoft documentation on Visual Studio C++ deployment includes:
deployment C++:
http://msdn.microsoft.com/en-us/library/zebw5zk9.aspx
Download redistributable package:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9b2da534-3e03-4391-8a4d-074b9f2bc1bf&displaylang=en
LikeLike
This is a great article on VBA. Can (intel) Fortran be linked to VBA as well ? I appreciate you enlightenment on this.
LikeLike
boroko – yes it can, in fact in some ways it’s easier. I’ve got a Fortran article in mind, but I don’t know when it will appear, it depends on when I have time to fit it in with stuff that actually earns me some money.
LikeLike
The other weird deployment issue seems to be related to the dll location. On some machines, I can reference it without a path when its in the same directory as the .xls file. On others this fails. It’s a little odd…
LikeLike
Pingback: Linking Excel to Fortran « Newton Excel Bach, not (just) an Excel Blog
Thank you very much. It works great.
LikeLike
Didn’t work.
I have office2003 and VC++2008, is it a bad combination?
I tried another tutorial about using VC++ functions in Excel and that didn’t work either. I got the error ‘#value’ with the explanation as: “Correct a #VALUE! error
Occurs when the wrong type of argument or operand is used.”
I am sure however about consistency in argument type between the two.
Any Ideas????
LikeLike
Sam – assuming you have both Office 2003 and VC++2008 correctly installed I don’t know what the problem is. Office 2003 shouldn’t be a problem, as far as I know.
LikeLike
I installed VS 2005 and now the whole thing is working as it is supposed to. It still could have been an installation problem is VS2008. Thanks alot.
LikeLike
Pingback: Linking AlgLib c++ to Excel VBA « Newton Excel Bach, not (just) an Excel Blog
i have a engineering optimization problem that ve modeled in excel, how can i solve the problem using a differential evolution algorithm since most of the source code i ve come across are not in vba.
please can i get a vb code that will link auto2fit software inorder to enjoy the benefit of DE algorithm in auto2fit software.
LikeLike
dami – I don’t have auto2fit, and I don’t know what facilities it has for integration with Excel. I suggest you contact the suppliers.
Alternatively you might want to have a look at the ALGLIB library which has a number of optimisation routines available in multiple languages including VBA and C++.
LikeLike
Hi,
I have the same problem as Tim’s 😦 I downloaded “Download redistributable package” but I still have a doubt:
where must be this package installed? In the computer where the dll is built or in the computer where the dll is going to be used?
Thank you and congrats for the blog!!! I find it very, very useful!!
LikeLike
JuanLuis – the computer where the dll is built will have the C++ compiler installed, so it won’t need the redistributable package. You need to install the package on any other machine that doesn’t have the compiler installed. If you then copy the dll file to the Windows\System32 directory (or somewhere else on the search path) it should work.
LikeLike
I was digging for a week how to make my VC++ work in Excel VBA using express 2010. This is the only post really worked, thanks a bunch.
The differences of your approach vs. most I’ve seen on other forum or youtube are mainly two places:1. __declspec(dllexport) in the header file; and 2. using the __stdcall(/GZ) option for compile.
#1 was in the walkthrough of MS website, but #2, seems so straightforward after one knows it, is mission impossible without an example. Again, thanks so much for this blog.
LikeLike
Thanks for a great introduction to interfacing Excel/Cpp/VBA
Using VC++ 10 and Excel 2007 I followed the instructions but had the following problems:
A) File MathFuncs2.cpp
1. Compiler error due to incomplete statement on Line 4 (file omitted).
#include
Solved by commenting out the line
2. Compile did not like line 22:
throw new invalid_argument(“b cannot be zero!”);
Solved by commenting out the line
3. Compiler did not like the namespace “std” with warning
Error: name must be a namespace name
but still compiled to produce the DLL
B) Running Excel
1. All functions give the error:
A value used in the formula is of the wrong data type
I Have checked that all the files are correct and the project properties have been set properly but still cannot get Excel to produce the correct answers.
Any help would be much appreciated.
LikeLike
Now working!
Sorry, I had a typo 😦
But points A1,A2 and A3 still seem to be a problem.
LikeLike
The WordPress software ate some of the text in the cpp file. The code sections were surrounded by code tags, but even so it seems to delete any text between angle brackets. I have now fixed it for the code in question, but it’s safer to use the download files, as these are the actual ones I have used in testing:
http://interactiveds.com.au/software/WalkThrough2.zip
LikeLike
douga4 – thanks for the quick response. Your files worked fine and I understand about WordPress eating your code 🙂
One final point. There still appears to be a problem with the line:
throw new invalid_argument(“b cannot be zero!”);
– On trying b=0, Excel crashes!
– On commenting out this line, the answer is 1.8E+308 (maximum double precision for a 64-bit machine), not #DIV/0 as one would get using a normal Excel calculation.
I have tried various try/catch options but none seem to do the trick?
LikeLike
Pingback: Daily Download 29: Using VBA – linking to C | Newton Excel Bach, not (just) an Excel Blog
Way cool! Some very valid points! I appreciate you writing this post plus the rest of the website is extremely good.
LikeLike
thank you for the tips , let me try
LikeLike