How to call C functions from Excel using a DLL written in C (or C++)

As part of my research, I wanted to combine the speed of C with the ability Excel provides to easily visualize and process substantial quantities of information. From the documentation and message boards scattered around the internet, along with a healthy does of experimentation, I was able to get it working quite well. What previously took 2 hours using only Excel and VBA (even with all the tricks I've learned over the years to make Excel about as fast as possible), now takes just seconds with the C functions doing most of the work. If the task is something you need to do a few hundred times, it can now be completed in an hour instead of a month. I prepared step-by-step instructions for myself so that I'll remember how to do it in the future and figured I'd put them online to help anyone else looking to do the same thing. To do everything described below I used Visual Studio 2008 (hereinafter VS08) and Excel 2010 64-bit (hereinafter Excel) on Windows 7 64-bit. Examples are available for download. An example of how to use the same DLL to call the C functions from Mathmatica (using version 9) is also included near the end of the page.

Update: I recently switched to Visual Studio 2012 and referred back to these notes when making changes to a C DLL I'd originally written using VS08. Some of the screenshots look different, but everything described below also work using VS12.

Links to the sections on this page:

• Debugging the DLL using VS08

• Release build/version (or more generally, a version that will work on other computers)

• C function with array arguments that "returns" array

• Console for displaying output

• Downloads

Creating a C DLL and using it in Excel

• 1. Open VS08

  2. Create a new project (File>New>Project)

     1. Select "Win32 Project"

     2. Name: “squareDLL” (or whatever you want)

     3. I let it “Create directory for solution”

• 1. Add new empty .c file (or download it here)

     1. Right-click the Source Files folder in the Solution Explorer (on the left side of the screen by default) and select Add>New Item

     2. Select “C++ File(.cpp)”

     3. Name: “square.c” (or whatever you want) (or .cpp for C++ file, in which case you’ll have to write the function slightly differently)

     4. Click Add

• 1. Type the contents of the C function

     1. As an example, I’m using: double _stdcall square (double *x) { return *x * *x; }

        1. The first "double" is the return type of the function

        2. The "(double *x)" means the argument x is a pointer to a double (in VBA we'll pass the argument "byRef")

        3. The function body, “return *x * *x;”, has a lot of * in it. Each “*x” is because the x is a pointer and we want to multiply the value of x (you could also use x[0] instead of *x). The middle * is just multiplication.

     2. You can also include files like the one at the top of the screenshot, but we don't need any for this, so I commented it out ("//#include <stdio.h>")

     3. If you're creating a C++ file instead (e.g., "square.cpp"), then the function would be: double _stdcall square (double &x) { return x * x; }

• 1. Add a definition file (or download it here)

     1. 1. Right-click the Source Files folder in the Solution Explorer and select Add>New Item

        2. Select “C++ File(.cpp)"

        3. Name: “defFIle.def” (you can change the defFile part, but need the .def extension)

        4. Click Add

• 1. Type the contents of the definition file

     1. File contents:

        • LIBRARY "square"

        • EXPORTS

        • squareForEXL = square

• • 1. Comments:

       • I'm not sure what the "square" on the first line does since the name of the DLL ends up being the project name (squareDLL). I think there are settings somewhere that deal with this.

       • "squareForEXL" is the name of the function in Excel. It can be the same as the name of the function in the C file ("square"). I just made it different as an example.

       • If you want to have more than one function available in Excel, you can include each additional function on its own line at the bottom (e.g., "myFunc2 = myFunc2" on a line after "squareForEXL = square")

• 1. Tell the compiler to use the definition file

     1. Right-click the project name “squareDLL” and select Properties to open the project properties

     2. Select Configuration Properties>Linker>Input

     3. Next to Module Definition File, type: defFile.def

     4. Click Apply

     5. Click OK

• 1. Compile (F7)

  2. If you're using 32-bit Excel (I think on either 32-bit or 64-bit OS), then the DLL is ready to go. If you're using 64-bit Excel, then you need to set it up to work in 64-bit Excel.

     1. Right-click the project name "squareDLL" and select Properties to open the project properties

     2. Select Configuration Properties>General

     3. Mine starts off looking like this

• 1. Compile again (F7)

  2. Open Excel (or download an example here )

  3. Open VBA (alt-F11)

  4. Insert a new module

  5. In the module, declare the C function so that it can be used by VBA and Excel

     1. In 64-bit Excel: "Private Declare PtrSafe Function squareForEXL Lib "C:\Users\Jon\Documents\Visual Studio 2008\Projects\squareDLL\x64\Debug\squareDLL.dll" (ByRef x As Double) As Double"

• • 1. In 32-bit Excel: "Private Declare Function squareForEXL Lib "C:\Users\Jon\Documents\Visual Studio 2008\Projects\squareDLL\Debug\squareDLL.dll" (ByRef x As Double) As Double"

    2. Obviously change the path to the location of your DLL

    3. You could put the entire "Declare" statement on one line instead of using the underscore after the function name

    4. The 64-bit Excel version has 2 main changes

       1. "PtrSafe" basically tells VBA that the function is safe for 64-bit

       2. VS08 changes the DLL location to the x64 folder, so make sure the path is to the 64-bit DLL

    5. A few comments on the "Private" before the "Declare"

• • • 1. The Private keyword is not required. In fact, the examples I've seen online don't use it

      2. Without the Private keyword you could use squareForEXL as a worksheet function (e.g., "=squareForEXL(E1)")

      3. However, when you use squareForEXL as a worksheet function, it results in errors. On my desktop, it returns the correct result (e.g., "= squareForEXL(10)" yields 100) but then gives an "Out of Stack Space" error, either at some point when calling the function or when Excel is closed. On my laptop, it returns an incorrect result (e.g., "= squareForEXL(10)" yields 0). On both, Excel sometimes crashes.

      4. If you debug this, the value in VS08 is not what it should be. My guess is that when the argument comes from the worksheet, the C function doesn't receive a pointer to a double like it's expecting.

      5. To solve this problem, I use an intermediate function in VBA. We're already using the Declare statement as an interface for the C function in the DLL. This just adds another step in VBA in between the C function and the worksheet. Instead of calling squareForEXL as a worksheet function, we call the VBA function squareOnWorksheet. The VBA function squareOnWorksheet takes a double as an argument, calls squareForEXL using this double as an argument, and then returns the value it gets back from squareForEXL. This way squareForEXL gets the double it is expecting and the worksheet gets the result it is expecting.

      6. Doing this, the worksheet function squareOnWorksheet always returns the correct value and there are no errors or crashes.

      7. The Private keyword makes squareForEXL Private to the VBA module so that you cannot (accidentally or otherwise) use squareForEXL as a worksheet function.

• 1. Use the function

     1. In VBA, you can use "squareForEXL(10)" and it should return 100 (see function "useSquareInVBA()" in screenshot above). Or you can use a VBA double variable as an argument.

     2. In Excel, you can type "=squareOnWorksheet(10)", the intermediate VBA function described above, and the cell value will be 100

• 1. Save this file if you want to use it in the next part about debugging

1. The C function, definition file, and Excel file described above are available for download here.

Debugging the DLL using VS08

• 1. Do the preceding steps to create the DLL and call it from Excel

  2. Set up debugging in VS08

     1. Right-click the project name “squareDLL” and select Properties to open the project properties

     2. Select Configuration Properties>Debugging

     3. In the dropdown list next to “Command”, select Browse (or you can type the file path manually)

     4. Find your Excel program (for me, it’s "C:\Program Files\Microsoft Office\Office14\EXCEL.EXE")

     5. Click Apply

• • 1. Click Apply

    2. Click OK

• 1. Add a break point to the function. This isn't necessary. It's just so that it stops in VS08 while running so you can see it's working

• 1. Press F5 to start debugging

     1. It might give a message like this:

• • 1. That's fine. Just click yes (and you can select "Don't show this dialog again" if you want)

    2. An instance of Excel opens

    3. Open the Excel file you saved before (or do the VBA steps above to create a new one that calls the function)

    4. Do something to make the function re-calculate (e.g., run a VBA procedure that calls the function, change the value in the cell that is used as the input, force recalculation with ctrl-alt-F9, press F2 in the cell with the formula and then press enter)

    5. It should take you back to VS08 to the break point you set

    6. If you put your mouse over the variable x, you should be able to see the value. Actually, it will show you the pointer value, but if you click the little “+” it will show you the value. Or you can add a line to the C file such as "double temp=*x;" before the break point and then put your mouse over the variable "temp" which has the value of x. Or you can add a watch.

Release build/version (or more generally, a version that will work on other computers)

Everything described above was for a debug build/version. The release build works on the computer you used to write the DLL, but doesn't work on all other computers. To fix this, do the following

• 1. Under Configuration Properties>C/C++>Code Generation, change "Runtime Library" from "Multi-threaded DLL (/MD)" to "Multi-threaded (/MT)".

1. I added the above screenshot after switching to VS12 (from VS08), so it might not look identical. But I did do this using VS08 and it worked. It might not solve every problem with building a Release version and/or using the DLL on other computers, but it worked for me.

C function with array arguments that "returns" array

Many functions you'd actually want to implement in this way will have array arguments and/or should return arrays. The implementation below is not the most robust implementation available (e.g., it doesn't check for or handle any errors, it "returns" an array as described below). There are more formal ways of doing this that involve Excel XLL add-in files (see links at the bottom), but these are significantly more involved (i.e., would require more than part of an afternoon to learn how to do). However, this implementation is quite easy. It requires little more than the steps described above for the simple "square" function. Everything described below is available for download here.

• 1. Everything about creating the DLL and using it in Excel is the same as the steps above. So create a DLL as described above. Or you can use the one you created before and add a new function to the C file (don't forget to also add it to the definition file). This can be done with or without the debugging described above. I created a new project called "arrayExcelCDLL" with C file "arrayFunc.c" (available below) and definition file "defFile.def" (only the definition file for "square" is available below, but you just have to change the names).

  2. Write contents of C function

     1. The C function looks like this

• • 1. The return type is int. It returns 0 if it successfully gets to the end. So while the function "returns" an array of results, it's not via the actual return value.

    2. The first argument, "double* dIn", is a pointer to a double that we'll treat like an 1 dimensional array of doubles. I loop through it with a double for loop as though it is a 2 dimensional array with dimensions given by "int* iSizeIn". It comes from a 1 or 2 dimensional array in Excel. It could be a non-array double in Excel, in which case the array is 1x1. To pass the pointer to the double array from VBA, we use the first element of the VBA double array as the argument.

    3. The second argument, "double* dOut", is a pointer to a double that we'll treat like a 1 dimensional array of doubles, similar to dIn. However, we'll use dOut to "return" results. It comes from a 1 or 2 dimensional array in Excel with dimensions given by "int* iSizeOut". The argument that is passed from excel is the first element of a double array (or a non-array double that's 1x1). It is passed by reference, so what is received by the C function is a pointer to the start of the location in memory for this array. We can write to the memory locations occupied by the array just as if it was an array we created in the C function. When the function returns to Excel, the values of the double array in VBA will be whatever we set in the C function. So while we're not actually returning an array of results, in practice we're "returning" an array of results. It might not earn you many style points from a professional programmer, but it works and it's a lot easier than the other methods I'm aware of that use XLL files.

    4. The function itself simply takes each element of dIn and stores the value multiplied by 10 in dOut to "return" to Excel. A real function could obviously do something more complicated. The size of dOut does not have to be the same as the size of dIn, so the function can be modified to achieve much more general goals.

• 1. Write the VBA code to use the C function

     1. To use the C function in the DLL, you need to declare it similarly to what we did above with the "square" function. In 64-bit Excel, this is accomplished by: "Declare PtrSafe Function useArray Lib "C:\Users\Jon\Documents\Visual Studio 2008\Projects\arrayExcelCDLL\x64\Debug\arrayExcelCDLL.dll" (ByRef dIn As Double, ByRef dOut As Double, ByRef iSizeIn As Long, ByRef iSizeOut As Long) As Long"

     2. The function is called by: "iWasError = useArray(dVals(1, 1), dRet(1, 1), iDimsIn(1), iDimsOut(1))"

        1. "iWasError" will become 0 if the function returns successfully

        2. "dVals(1, 1)" is the first element of the double array that holds the arguments, corresponding with dIn

        3. "dRet(1, 1)" is the first element of the array that will be modified to hold the results, corresponding with dOut

        4. "iDimsIn(1)" is the 1x2 array that holds the height and width of dVals

        5. "iDimsOut(1)" is the 1x2 array that holds the height and width of dRet

     3. The example file (available below) takes values from a range starting in cell A3 on the worksheet, calls the C function, and then puts the returned results (the original values multiplied by 10) in the cells to the right of the original values.

     4. It is currently set up with a 10x10 array with the numbers 1 through 100 and takes between 0.001 and 0.01 seconds on my computer. I've also tested it with a single value in cell A3 or with 10 columns x 100,000 rows of values. In the later case with 1 million values, it takes between 0.5 and 0.8 seconds on my computer (and only about 1 to 1.3 seconds on my old laptop).

1. The C function, definition file, and Excel file described above are available for download here.

Console for displaying output

If you want a console to display output or to help with debugging, you can open one from the C DLL.

• 1. Include "#include <stdio.h>" at the top of the DLL

  2. At the start of the function, include the following lines of code

     • AllocConsole()

     • freopen("CONIN$", "r", stdin);

     • freopen("CONOUT$", "w", stdout);

     • freopen("CONOUT$", "w", stderr);

  3. Then in your code you can do things like: fprintf(stdout, "iVar: %d\n", iVar);

  4. To make the console stay open so that you can see the output, at the end include: getchar();

  5. Close the console using: FreeConsole();

Calling C DLL from Mathematica

On this page, I describe how to call the C functions in the DLLs above from Mathematica.

Links

To figure out everything above, I started from various examples I found online and then experimented. I'll provide links to a few here in case you want to read more and to give credit where credit is due.

• 1. If you'd like to learn the first step of creating the "square" function by watching a video, watch this video on youtube. This is where I first learned the general technique so most of the steps are the same. The main exceptions are the part about 64-bit excel and the way I call the function from the worksheet using the intermediate VBA function to avoid the errors I got doing it the way shown in the video. If you click on his name, he also has a video about debugging.

  2. I figured out how to do the example with array arguments and results through experimentation, but after figuring it out, I came across this example which does something very similar using C++.

  3. Microsoft has documentation available here which is mostly about developing XLLs, but it can be useful for this and other topics with VBA.

  4. There is a book by Steve Dalton. The newest edition I found was called Financial Applications Using Excel Add-in Development in C/C++. The link is just to a goolge search. The book is mostly about XLLs, but it is mentioned on many message boards with information about other useful topics.

  5. There are many others--Google it. I'm not sure how we learned any of this before message boards, blogs, and the rest of the internet. It certainly took longer than an afternoon.

Downloads

The square example is available for download here.

The array example is available for download here.