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):

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.

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.

LikeLike

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