In the previous post I decribed how to create four simple functions in Visual C++, and how to link them to an Excel spreadsheet or a VBA User Defined Function (UDF).
This post will cover:
- 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.
To pass an array from a C function back to a VBA routine:
Declare the array in the function and header file as shown below:
int Quad(double a, double b, double c, double* resa)
extern __declspec(dllexport) int Quad(double a, double b, double c, double* resa);
Add data to the array:
resa[0] = (-b-disc )/(2*a);
resa[1] = (-b+disc )/(2*a);
Modify the VBA declare statement to pass the array by reference:
Declare Function Quad Lib “D:\Users\Doug\Documents\Visual Studio Projects\MSDN-wt2\Debug\MSDN-wt2” (ByVal a As Double, ByVal b As Double, ByVal c As Double, ByRef Res As Double) As Long
In the VBA function caling the C function, dimension the array, and pass the first item in the array in the function call:
Dim Res(0 To 1) As Double
..
Retn = Quad(a, b, c, Res(0))
The contents of the array Res() will now be available to the VBA function. The full code is shown below, and has been added to WalkThrough2.zip
Header file:
// MSDN-wt2.h
// Function returns number of real roots
// resa contains roots
extern __declspec(dllexport) int Quad(double a, double b, double c, double* resa);
.cpp file
// MSDN-wt2.cpp
#include "MSDN-wt2.h"
#include
#include
using namespace std;
double disc, discsq;
int Quad(double a, double b, double c, double* resa)
{
discsq = ( b*b - 4*a*c);
if (discsq < 0)
{
return 0;
}
disc = sqrt(discsq);
if (discsq = 0)
{
resa[0] = (-b )/(2*a);
resa[1] = resa[0];
return 1;
}
resa[0] = (-b-disc )/(2*a);
resa[1] = (-b+disc )/(2*a);
return 2;
}
VBA code:
Declare Function Quad Lib "D:\Users\Doug\Documents\Visual Studio Projects\MSDN-wt2\Debug\MSDN-wt2" (ByVal a As Double, ByVal b As Double, ByVal c As Double, ByRef Res As Double) As Long
'
Function XLQuad(a As Double, b As Double, c As Double) As Variant
Dim Res(0 To 1) As Double
Dim Retn As Long
Retn = Quad(a, b, c, Res(0))
XLQuad = Res
End Function
The next example illustrates use of code from The GNU Scientific Library:
In order to use the GSL code:
Set up a new project as before.
Create .h and .def files
Create a blank .cpp file, paste in the GSL code from poly/solve_cubic.c, and modify it as follows:
Replace the line:
#include
with
#include "Cubic.h"
modify the function declaration to:
int gsl_poly_solve_cubic (double a, double b, double c, double* xa)
Replace all instances of x0, x1, and x2 with xa[0], xa[1] and xa[2]
Create a folder named gsl under your Visual Studio include folder, and copy over all the applicable .h files from the GSL download. These are scattered over several folders, but if you miss any the compiler will raise an error with the missing file name(s), so you can copy and try again.
The dll file can now be created, and declared in a VBA module as before:
Declare Function gsl_poly_solve_cubic Lib "D:\Users\Doug\Documents\Visual Studio Projects\Cubic\release\Cubic" (ByVal a As Double, ByVal b As Double, ByVal c As Double, ByRef xa As Double) As Long
All the C and VBA code for the functions described above has been added to WalkThrough2.zip . Download and use the code from the zip file, rather than copying and pasting from this post, as the html tends to mangle the computer code.
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Linking Excel to C - 3; Avoiding bottlenecks « Newton Excel Bach, not (just) an Excel Blog
Pingback: Linking Excel to Fortran - 2 « Newton Excel Bach, not (just) an Excel Blog
If you want a simple way to call C/C++/Fortran from Excel, you may want to check out http://xll.codeplex.com.
LikeLike
Certainly worth a look kalx. Another site currently devoting some time to getting Excel and C++ talking to each other is Charles Williams’ Fast Excel Blog:
From Part 1:
“This is the first of a series of posts about my perilous voyage from VBA to C++.
I am starting with the (current) design of FastExcel V3 SpeedTools Beta 2 (bit of a mouthful so lets just call it SpeedTools).
SpeedTools contains a library of over 80 additional Excel functions as well as additional calculation modes beyond Automatic and Manual.
SpeedTools has to support all Excel versions from Excel 2000 to Excel 2010 64-bit and Excel VNext (I don’t know what MSoft are going to call it!).”
LikeLike
I’ve made a few posts in that thread dougaj4.He seems to be cottening on to how much faster xll’s can be than VBA. He is absolutely right about that.
LikeLike
Pingback: Daily Download 29: Using VBA – linking to C | Newton Excel Bach, not (just) an Excel Blog
Thx for this post but i don’t understand why you redefine the gsl_poly_solve_cubic? You can’t import the GSL Lib in your project?
LikeLike
If you are using the C SDK, just include the header, call the function, and link to the lib. In VBA you have to Declare each function in the dll you wish to use. If you have a static lib you are SOL.
LikeLike
Guillaume – I’m not sure I understand the question, but the purpose of the exercise is to write a VBA function that calls a function from the GNU Library, without using the C SDK, or any other extras.
Keith – can you expand on: “If you have a static lib you are SOL” I don’t know what that means.
LikeLike
VBA Declare uses LoadLibrary and GetProcAddress. Those don’t work with static libs.
Extras are a good thing if you know how to take advantage of them. It is a modest effort, but http://xllgsl.codeplex.com lays the breadcrumbs for how to cut out the COM bottleneck you incur when you use VBA.
If you are using arrays of doubles, you have to copy everything back and forth. See http://xllgsl.codeplex.com/SourceControl/latest#trunk/poly.cpp for how to get Excel to pass you a pointer to the double array that you can hand off to a C function. No copying is involved.
LikeLike
Wikipedia has a remarkably good entry on this http://en.wikipedia.org/wiki/Static_library.
“This avoids dependency problems, known colloquially as DLL Hell or more generally dependency hell. Usually, static linking will result in a significant performance improvement. Static linking can also allow the application to be contained in a single executable file, simplifying distribution and installation.”
This is precisely why I always link my xlls with static libs, an option not available with VBA.
LikeLike
We’ve been through this before, but I’ll just note:
– In all of my applications the total time for the VBA to run is less than a second, usually a few milliseconds, so the potential benefit of speeding up this part of the routine is negligible. Execution time of the complied part on the other hand is often minutes, and sometimes hours, so finding the best routines, and using them efficiently certainly is worthwhile.
– Arrays are passed to the compiled routines as a pointer.
– Programming is not my job, it’s just a tool I use to get my work done. It’s not worth my time spending a significant effort learning new techniques that will save milliseconds.
– I’m sure there are many people for whom the xll route is worth the effort, but for me (and others in a similar situation) writing the interface routines in VBA is a huge time saver (in programming time) for negligible loss of efficiency in the finished product.
LikeLike
All I can do is read what you write Doug. I try to understand what you are saying and respond to that. Maybe “we’ve been through this before” means you will simply dismiss anything I say, but I’ll give it another go. If you don’t want me to post on your site, just let me know. I’m the easiest guy in the world to get rid of.
– Your latest post was about how you sped up your VBA code from 4 minutes to less than one second. How long did you spend writing that code? You could have used http://xllrange.codeplex.com, but you’d have to learn something about array languages. It uses notions from APL to factor complicated problems into simple operations. Ken Iverson was a genius.
– Arrays in VBA have to be marshaled across the COM boundary. You are an engineer, let’s measure things. How about you write a routine to do a Cholesky decomposition of a 1000×1000 matrix. Or pick anything similar that you have already done. Happy to do a VBA vs. xll throwdown if that is what it takes to convince you.
– You seem to enjoy programming and learning what other smart people in the world have to say. You may be overestimating the difficulty of learning modern C++. It is 90 minutes long, but the man himself has been committing his time to making C++ easier to use: http://channel9.msdn.com/Events/GoingNative/GoingNative-2012/Keynote-Bjarne-Stroustrup-Cpp11-Style
– “Negligible loss in efficiency” If you are just solving toy problems, then stick with VBA. I’ve made a lot of money converting VBA projects to C++ because my clients got frustrated with how slow VBA is. And even more by selling them a platform independent C++ library they can hook into their production systems. Try calling VBA from Java or C#.
Monogamy in programming languages is not a virtue, IMHO.
LikeLike
Keith, you are welcome to post here, but you don’t seem to have read what I wrote. I’m not talking about toy problems; problems that take hours of solution time in an efficient commercial compiled solver are not toy problems. What I’m saying is that the quickest way to run these programs, for me, is to create the input data in Excel, transfer it to the solver using VBA, then transfer the results back to Excel using VBA. Typically the VBA part will take milliseconds. Where is the scope for significant improvement by removing the VBA step?
The other point is that often VBA is slow because of the way it is implemented. The example of reducing 4 minutes run time down to less than a second is a good case in point. The additional coding took about 10 minutes, there are about ten lines of code different from the slow version. No doubt some people could do it in 5 minutes, and reduce the run time to less than 0.1 seconds using a different language, but for me, for that problem, 10 minutes coding for a better than 250x speed improvement is more than good enough.
Finally, I’m not advocating “monogamy” in programming languages, just offering solutions that work for me.
LikeLike
Like I said, try to do a 1000×1000 Cholesky decomposition in VBA using any commercial library you please. I’ll send you my xll, or you can build it yourself from https://xlllapack.codeplex.com. Happy to eat crow if I’m wrong, but I don’t think you will need the timers described at https://xllutility.codeplex.com to tell the difference.
Sounds like you haven’t hit the wall with VBA yet. I assure you it is there and I have the bruises to prove it.
If you want to share your work with others, xll’s win hands down. No need for hard-wired path names (as you have above), you can link with static libs to make it self-contained, and you can automatically generate documentation for all of your functions that is integrated into the Excel help system. (Help on this function from the Function Wizard.) It is so good it even makes bad code look professional. 🙂
LikeLike
OK, here are the times for inverting a 1000×1000 array of random doubles on the spreadsheet, using VBA to call Alglib C++ code (times in seconds):
Convert 1000×1000 range to Double array 0.20
Invert matrix with Alglib C++ routine 0.96
Convert 1D array to 2D in VBA 0.04
Assign array to function return value 0.00
Total 1.19
Percentage VBA 20%
Even that is worse than I would get in a typical application, because I would never generate a 1000 x 1000 array on the spreadsheet and then read that into VBA, which is where the great majority of the VBA time is in this case. Typically I would read a fairly small amount of data from the spreadsheet, generate the matrix in VBA (often much bigger than 1000×1000), solve in C++ (or Fortran, or C#), extract the results I need in VBA, then read that back to the spreadsheet as an array.
The spreadsheet used can be found at:
https://newtonexcelbach.wordpress.com/2010/05/27/compiled-alglib-matrix-functions-for-excel/
If you would like to send your xll, I’d be interested to see how it compares.
LikeLike
Have a look at http://kalx.net/xlllapack.zip. You might need to right click on the chm file, properties, and unblock for it to work.
The xll should work on any 32-bit Windows OS and Excel 2007 or later.
The getri.xlsx spreadsheet shows how to invert a 3×3 random matrix. I’m not sure how you are doing your timings, but when I run it on a 1000×1000 matrix on my 2.4GHz laptop I’m getting 2e-5 seconds for the inverse. I’m sure Excel is doing something behind the scenes, but it hands me the pointer to the array of doubles, so I’m counting that as zero seconds.
Some guy named Doug Jenkins wrote vbmat.cpp. I wrote https://xlllapack.codeplex.com/SourceControl/latest#trunk/getri.cpp
It is about the same amount of code. You read ALGLIB documentation to do that, I read LAPACK documentation. 1.19/2e-5 = 5950. I’d be interested to see the timings on your machine.
Frankly, I’m shocked by the 6k speedup. I knew it would be faster, but not by that factor. Maybe I’m missing something, but objective analysis can get to the bottom of this. Computers just do what you tell them to do and it is straightforward to measure their performance.
LikeLike
Thanks for that.
I don’t believe 20 microseconds for inverting a 1000 x 1000 matrix (which is actually 59,500 times faster than 1.19 sec).
On my machine I get about 0.7 seconds, which sounds much more reasonable. I timed it by writing the time to a cell, recalculating, then writing the time again (from VBA).
Can you call xll functions from VBA?
LikeLike
Yikes, I have no idea what I was looking at last night. In the cool light of Monday morning, it looks like the correct timing is 0.3 seconds. 1.19/0.3 ~= 4, assuming I got that right. (There are three kinds of mathematicians, those who can count, and those who can’t count.)
The (~30MB) sheet is http://kalx.net/getri.xlsx. You will need xllutility.xll from http://kalx.net/xllutility.zip for the timing.
The TIMER.* functions are in in Sheet3!ALN1-3 and documented at https://xllutility.codeplex.com.
To call an xll from VBA use Application.Run(“Function”, arg1, …)
I’d love to see a comparison running on the same hardware. After unzipping AL-Matrixdll2 into a folder, then opening Matrixdll2.xlsb with 32-bit Excel 2013 I’m seeing #VALUE!s when I try to recalc. What am I doing wrong here?
LikeLike
Not sure why that post was sent for approval, but now approved.
I’m a bit busy at the moment, but I will have a look and report back.
LikeLike
Timing a full recalculate on the Getri spreadsheet I get about 0.5 seconds on my machine.
I also tried calling it from VBA with the following code:
Sub TimeCalc2()
Dim RandA As Range, ResA As Variant
[ALO2] = Timer
Set RandA = Range(“randa”)
[ALO3] = Timer
ResA = Application.Run(“GetRI”, RandA)
[ALO4] = Timer
Range(“resa”).Value = ResA
[ALO5] = Timer
Application.Calculate
End Sub
which gave:
Set RandA = range(“randa”): 0 sec.
Run Getri: 0.35 sec.
Write ResA to spreadsheet: 1.4 sec.
Total 1.75 seconds
So it looks like the actual matrix inversion routine is about twice as quick as the one I’m using, and the data transfer is much quicker when working on the spreadsheet (or presumably if everything is done in C++), but slower if the routine is called from VBA, and the results are then copied to the spreadsheet.
I’m not convinced that the actual solution time has anything to do with the VBA side of it. The data is passed by reference to the first element of the array, as a double, and the results are also passed back by reference, so where does the delay come in that stage?
Also, the example doesn’t really reflect the way I work. I would never generate a large array on the spreadsheet, then transfer that to VBA, then to the compiled routine. Typically there would be a much smaller amount of data on the spreadsheet, and the array would be generated either in VBA, or if that was too slow for a particular application, in the compiled code. Similarly the results returned to the spreadsheet would be a small sub-set of the output data, so the time taken in transferring data from the spreadsheet and back really isn’t an issue.
LikeLike
Thanks Doug. I appreciate your taking the time to run this on your machine.
I don’t have the source code to Excel, but I’m pretty sure the array sitting in Excel is copied into the VBA variant across the COM boundary. That would explain the timings you are seeing.
LikeLike