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:

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

New Project 1

The Win32 Application Wizard will open, click Next>

New Project 2

New Project 2

Select Dll and Empty project, and click Finish.

New Project 3
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

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
Properties 1

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

Properties 2

Properties 2

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

Properties 3

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

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 end result

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

Advertisements
This entry was posted in Excel, Link to dll, UDFs, VBA and tagged , , , . Bookmark the permalink.

28 Responses to Linking Excel to C

  1. Rick Williams says:

    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

    Like

  2. Pingback: Linking Excel to C « Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Excel links of the week [Sep 8] | Pointy Haired Dilbert - Chandoo.org

  4. Tim K says:

    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 🙂

    Like

  5. dougaj4 says:

    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)

    Like

  6. Tim K says:

    I think I’ve solved it – it requires the visual c++ 2008 redistributable files installed to work

    Like

  7. dougaj4 says:

    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

    Like

  8. boroko says:

    This is a great article on VBA. Can (intel) Fortran be linked to VBA as well ? I appreciate you enlightenment on this.

    Like

  9. dougaj4 says:

    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.

    Like

  10. Tim K says:

    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…

    Like

  11. Pingback: Linking Excel to Fortran « Newton Excel Bach, not (just) an Excel Blog

  12. Lou Bruso says:

    Thank you very much. It works great.

    Like

  13. Sam says:

    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????

    Like

  14. dougaj4 says:

    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.

    Like

    • Sam says:

      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.

      Like

  15. Pingback: Linking AlgLib c++ to Excel VBA « Newton Excel Bach, not (just) an Excel Blog

  16. dami f says:

    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.

    Like

  17. dougaj4 says:

    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++.

    Like

  18. JuanLuis says:

    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!!

    Like

  19. dougaj4 says:

    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.

    Like

  20. Song says:

    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.

    Like

  21. GrahamG says:

    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.

    Like

  22. GrahamG says:

    Now working!
    Sorry, I had a typo 😦
    But points A1,A2 and A3 still seem to be a problem.

    Like

  23. GrahamG says:

    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?

    Like

  24. Pingback: Daily Download 29: Using VBA – linking to C | Newton Excel Bach, not (just) an Excel Blog

  25. Way cool! Some very valid points! I appreciate you writing this post plus the rest of the website is extremely good.

    Like

  26. nichom says:

    thank you for the tips , let me try

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s