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:

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:

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:

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

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.

Pingback: Using Regasm.exe | Newton Excel Bach, not (just) an Excel Blog

Pingback: Frame4; now with added Alglib | Newton Excel Bach, not (just) an Excel Blog

Pingback: Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions | Newton Excel Bach, not (just) an Excel Blog

Pingback: Daily Download 13: The ALGLIB maths library and Excel | Newton Excel Bach, not (just) an Excel Blog