Newton Excel Bach, not (just) an Excel Blog

This is a follow on from the previous post, showing how the C# version of the ALGLIB maths library can be accessed from Excel, with very little additional coding.  The download file, Matrixdll-c#.zip includes compiled .dll and .tlb files which can be used immediately with the spreadsheet (Matrixdll-c#.xlsb) in conjunction with the Alglib compiled dll, alglibnet2.dll, but this post first describes how to set up the necessary linking code in VBA and Visual Studio Express C#.

The first step is to set up a new project in C# with the necessary COM links enabled, exactly as described in the previous post.  For this example I have set up a project called ALMatrixLib, with a single class called AlgLibMatrix, which will contain all the code linking to the matrix functions.

To test the system was working I first entered a short function not requiring the Alglib library:

Code for simple quadratic equation solver

Having successfully compiled that code it is necessary to add a few lines of VBA code in Excel to pass the data to the C# code:

Function ALSolveQuad(a As Double, b As Double, c As Double) As Double
Dim ALSolve As New AlgLibMatrix

ALSolveQuad = ALSolve.solvequad(a, b, c)

End Function

Note that the Alglib function is declared as a new instance of the Class AlgLibMatrix, and is called with the line: ALSolveQuad = ALSolve.solvequad(a, b, c).  The same procedure is used for all the other functions included in the AlgLibMatrix class.

The other essential step is to provide a reference to the compiled AlgLib code in the ALMatrixLib project.  The simplest way to do this is to open the solution Explorer window, then simply drag the alglibnet2.dll file from Windows Explorer (or your file manager of choice) onto the References heading:

Reference to alglibnet2.dll

Then all that remains is to enter the code to call the AlgLib functions. The first two examples are shown below and full open source code for the interface functions is included in the download file. The AlgLib library is also open source and may be downloaded from their site.

 // Inverse of real matrix
public double [,]    alrminverse(double[,] ddata, out int info)
        {
            alglib.matinvreport rep;
alglib.rmatrixinverse(ref ddata, out info, out rep);
return ddata ;
        }

        // Inverse of real matrix using LU decomposition
public double[,] alrmluinverse(double[,] ddata, int m, out int info)
        {
            int[] pivots;
alglib.rmatrixlu(ref ddata, m, m, out pivots);
            alglib.matinvreport rep;
alglib.rmatrixluinverse(ref ddata, pivots, out info, out rep);
return ddata;
        }

The VBA code for the same two functions is:

Function ALRMatInv(MatrixA As Variant) As Variant
'  Inverse of real matrix
Dim n As Long, M As Long, Info As Long
Dim a2() As Double, iErr As Long, ResA As Variant
Dim MatInv As New AlgLibMatrix

iErr = VarAtoDouble2D_0(MatrixA, a2, M, n)

If iErr = 0 Then
ResA = MatInv.alrminverse(a2, Info)

If Info = 1 Then ALRMatInv = ResA
    Else
ALRMatInv = "Invalid data"
    End If
End Function

Function ALRMatLUInv(MatrixA As Variant) As Variant
' Inverse of real matrix using LU decomposition
Dim n As Long, M As Long, n2 As Long, Info As Long
Dim a2() As Double, iErr As Long, ResA As Variant
Dim MatInv As New AlgLibMatrix

iErr = VarAtoDouble2D_0(MatrixA, a2, M, n)
If iErr = 0 Then
ResA = MatInv.alrmluinverse(a2, M, Info)

If Info = 1 Then ALRMatLUInv = ResA
    Else
ALRMatLUInv = "Invalid data"
    End If
End Function

Having compiled the C# routines, and entered the VBA code, the C# routines should be linked to Excel using the VBE Tools-References menu, as described in the previous post.  The AlgLib functions should now be available in Excel under the User Defined category.  The screen-shot below shows a list of the available functions:

List of Matrix Functions in Matrixdll-c#.xlsb

Of particular interest is the sparse linear equation solver which provides an efficient solution to the systems of equations set up in structural analysis. A typical example is shown below; note the speed increase from the all VBA solution to the C# sparse matrix solver function (752 times speed increase!):

Sparse matrix solver example.

For those not wanting to delve into VBA and C# code the download file contains compiled versions of the routines described above, which together with the included spreadsheet and the AlgLib dll file (from the AlgLib site) will allow all the functions to be accessed without any coding.  It is however necessary to register the dll files with the .Net Framework, and the procedure for doing that will be described in the next post.