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
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.
Newton Excel Bach, not (just) an Excel Blog 