Linking Excel to C – 3; Avoiding bottlenecks

The previous post in this series included a C dll to solve cubic polynomial equations that could be called from VBA.  The performance of this routine is compared with a VBA routine using a similar algorithm in the sceenshot below (rows 6 and 8):

Benchmark of cubic solution routines

Benchmark of cubic solution routines

Both of these routines spend most of their time passing data from the spreadsheet to VBA and back again, as can be seen by comparing with row 7, where the routine VBAcubica is over 2 times faster than the original VBA routine. In VBAcubica all 1000 lines of data are passed to VBA as an array in one operation; the equations are then solved and the results passed back in one operation. This spreadsheet and the VBA and C code can be downloaded here:

This process has been replicated using the C dll in three different ways:

  • In Cubica the data is passed to VBA as an array, then passed to the dll one row at a time as three doubles, passed by value.
  • In Cubica2 the 1000×3 array of variants is converted to a single dimension, base 0, array of doubles, which is passed to the dll by reference.
  • in Cubica3 the VBA array of variants is converted to a 3×1000, base 1, array of doubles which is again passed to the dll by reference.

It can be seen that the the three dll routines that use an array to pass the data from the spreadsheet to VBA are much faster than the other routines, and that there is no significant difference between them.

The VBA declare statements, and the VBA statements calling the dll functions are shown below:

' Amend paths in the declare statements below as necessary
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
Declare Function gsl_cubica Lib "D:\Users\Doug\Documents\Visual Studio Projects\Cubic\Release\Cubic" (ByVal numrows As Long, ByRef abc As Double, ByRef xa2 As Double) As Long

Function Cubica(CubicData As Variant) As Variant
..
For i = 1 To numrows
a = CubicData(i, 1)
b = CubicData(i, 2)
c = CubicData(i, 3)

Retn = gsl_poly_solve_cubic(a, b, c, xa(0))
..

Function Cubica2(CubicData As Variant) As Variant
.. For i = 0 To numrows - 1
For j = 0 To 2
abc(i * 3 + j) = CubicData(i + 1, j + 1)
Next j
Next i
Retn = gsl_cubica(numrows, abc(0), xa_2(0))
..

Function Cubica3(CubicData As Variant) As Variant
..
For i = 0 To numrows - 1
For j = 0 To 2
abc(j, i) = CubicData(i + 1, j + 1)
Next j
Next i
Retn = gsl_cubica(numrows, abc(1, 1), xa_2(0))
..

Note that cubica2 and cubica3 call the same function in cubic.dll, even though they are passing arrays of different dimensions, with a different base.

Full VBA and C code for all the functions is included in the download file.

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

2 Responses to Linking Excel to C – 3; Avoiding bottlenecks

  1. dougaj4 says:

    I have just run the benchmark times in Excel 2000, then returned to 2007 and run them again, and found all but one of them about twice as fast as before.

    I’m not sure what is going on there, but now the 2000 times are only about 50% faster, rather than 200% faster.

    If anyone has an explanation I’d be interested. In all cases the file was saved, closed, then re-opened and run without opening the VB Editor.

    Like

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

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