The previous post described how to install AlgLib VBA routines in Excel.  It is also possible to make AlgLib c++ routines (compiled as dll’s) available from Excel, either from VBA, or using User Defined Functions (UDFs) directly from the spreadsheet.  Using compiled dll routines provides the following advantages:

• Performance is much better, especially in calculation intensive tasks such as matrix inversion or extraction of Eigen values.
• The spreadsheet files will be much smaller.
• Better security

This post describes the required operations to compile the C++ code to a dll file, and link to it from VBA, using matrix inversion and Eigen value routines as examples.  The Excel and necessary additional C++ files, and the compiled dll file, may be downloaded from AL-Matrixdll.zip, which includes full open source code (other than the AlgLib code).  The C++ was compiled using Visual Studio C++ Express 2010.  This is a free download (for non-commercial use), of for those not wishing to install this package the dll files may be used after installation of the C++ Redistributable Package (link corrected 27 May 2010).

The basic procedure for linking C++ routines to VBA (using Visual Studio c++ 2008) is described here: Linking Excel to C.  The procedure for the 2010 version is identical.  Useful additional information on debugging the routines linking to Excel can be found here.

The procedure to link VBA to the AlgLib routines requires the following steps:

1. Write VBA routines to prepare the Excel data in a suitable format, and call the AlgLib interface routines (see step 2).
2. Write C++ interface routines to convert the VBA data to AlgLib specific data types, call the AlgLib routines, and convert the resulting data back to a format suitable for transfer to VBA.
3. Write the necessary C++ .h and .def files, to allow the export of the c++ functions to a form accessible from VBA.

Example code is shown below, and is included in the download file.

VBA Matrix Inversion Functions

Declare Function vbcmatinv Lib "..\VBA-DLL\Alglib2.dll" (ByRef A2 As Double, ByVal M As Long) As Long
Declare Function vbrmatinv Lib "..\VBA-DLL\Alglib2.dll" (ByRef A2 As Double, ByVal M As Long) As Long

Function RMatInvdll(a As Variant) As Variant
  Dim N As Long, M As Long, N2 As Long, Pivots() As Long
  Dim A2() As Double, i As Long, J As Long, K As Long, Rtn As Long

  If TypeName(a) = "Range" Then a = a.Value2
  M = UBound(a)
  N = UBound(a, 2)

  ' Copy 2D base 1 variant array to 1D base 0 double array
  ReDim A2(0 To M * N - 1)
  For i = 1 To M
        For J = 1 To N
  A2((i - 1) * M + J - 1) = a(i, J)
        Next J
  Next i

  Rtn = vbrmatinv(A2(0), M)

  ' Copy 1D base 0 array to 2D base 1 array
  For i = 1 To M
        For J = 1 To N
  a(i, J) = A2((i - 1) * M + J - 1)
        Next J
  Next i
    ' Assign array to function return value
  RMatInvdll = a
End Function

Function CMatInvdll(a As Variant) As Variant
  Dim N As Long, M As Long, N2 As Long, Pivots() As Long
  Dim A2() As Double, i As Long, J As Long, K As Long, Rtn As Long

  If TypeName(a) = "Range" Then a = a.Value2
  M = UBound(a)
  N = UBound(a, 2)

  ' Copy 2D base 1 variant array to 1D base 0 double array
  ReDim A2(0 To M * N - 1)
  For i = 1 To M
        For J = 1 To N / 2
  A2((i - 1) * N + (J - 1) * 2) = a(i, (J - 1) * 2 + 1)
  A2((i - 1) * N + (J - 1) * 2 + 1) = a(i, (J - 1) * 2 + 2)
        Next J
  Next i

  Rtn = vbcmatinv(A2(0), M)

  ' Copy 1D base 0 array to 2D base 1 array
  For i = 1 To M
        For J = 1 To N / 2
  a(i, (J - 1) * 2 + 1) = A2((i - 1) * N + (J - 1) * 2)
  a(i, (J - 1) * 2 + 2) = A2((i - 1) * N + (J - 1) * 2 + 1)
        Next J
  Next i
    ' Assign array to function return value
  CMatInvdll = a
End Function

VBA Eigen Value and Eigen Vector Functions

Declare Function vbrmatEVD Lib "..\VBA-DLL\Alglib2.dll" (ByRef A2 As Double, ByVal N As Long, ByVal Vect As Long, ByRef ResV As Double) As Long

Function EigenvRdll(a As Variant, Optional Vect As Long = 0) As Variant
    Dim N As Long, M As Long, D() As Double, Z() As Double
  Dim A2() As Double, i As Long, J As Long, K As Long, ResV() As Double, Res As Long, ResRows As Long

  If TypeName(a) = "Range" Then a = a.Value2
  N = UBound(a)
  Select Case Vect
    Case 0
  ResRows = 2
    Case 1
  ResRows = N + 2
    Case 2
  ResRows = N + 2
    Case 3
  ResRows = N * 2 + 2
    End Select

  ReDim A2(0 To N * N - 1)
  ReDim ResV(0 To N * ResRows - 1)
  ReDim ResA(1 To ResRows, 1 To N)
    K = 0
  For i = 1 To N
        For J = 1 To N

  A2((i - 1) * N + J - 1) = a(i, J)

        Next J
  Next i

  Res = vbrmatEVD(A2(0), N, Vect, ResV(0))

  For i = 1 To ResRows
        For J = 1 To N
  ResA(i, J) = ResV((i - 1) * N + J - 1)
        Next J
  Next i
  EigenvRdll = ResA

End Function

C++ Interface Functions

// vbmat.cpp -- Interface for VBA matrix functions.

#include "vbmat.h"

int vbrmatinv(double* av, int n)
{
int i,j,info;
matinvreport rep;
ap::real_2d_array a2;

a2.setcontent(0, n-1, 0, n-1, av);
rmatrixinverse(a2, n, info, rep);

for( i = 0; i < n; i++){
		for (j=0;j<n;j++) {
av[i*n+j] = a2(i,j);
		}
	}
	return 0 ;
}

int vbcmatinv(double* av, int n)
{
int i,j,k,info;
matinvreport rep;
ap::complex_2d_array a2;

a2.setlength(n, n);

for( i = 0; i < n; i++){
		for (j=0;j<n;j++) {
k = i*n*2+j*2;
a2(i,j).x = av[k];
a2(i,j).y = av[k+1];
		}
	}

cmatrixinverse(a2, n, info, rep);

for( i = 0; i < n; i++){
		for (j=0;j<n;j++) {
k = i*n*2+j*2;
av[k] = a2(i,j).x ;
av[k+1] = a2(i,j).y ;
		}
	}

	return 0 ;
}

int vbrmatEVD(double* av, int n, int vect, double* resv)
{
int i,j, res;
	ap::real_2d_array a2;
ap::real_2d_array vl;
ap::real_2d_array vr;
	ap::real_1d_array wr;
	ap::real_1d_array wi;

	a2.setcontent(0, n-1, 0, n-1, av);

res = rmatrixevd(a2, n, vect, wr, wi, vl, vr);

for( i = 0; i < n; i++){
resv[i] = wr(i);
resv[i+n] = wi(i);
	}
if (vect == 1)
	{
for( i = 0; i < n; i++){
			for( j = 0; j < n; j++){
resv[(2+i)*n+j] = vr(i,j);
			}
		}
	}
if (vect == 2)
	{
for( i = 0; i < n; i++){
			for( j = 0; j < n; j++){
resv[(2+i)*n+j] = vl(i,j);
			}
		}
	}
if (vect == 3)
	{
		for( i = 0; i < n; i++){
			for( j = 0; j < n; j++){
resv[(2+i)*n+j] = vr(i,j);
resv[(2+n+i)*n+j] = vl(i,j);
			}
		}
	}

	return res ;
}

C++ .h file

// vbmat.h
#include "ap.h"
#include "matinv.h"
#include "evd.h"

// Returns av matrix
extern __declspec(dllexport) int vbrmatinv(double* av, int n);
extern __declspec(dllexport) int vbcmatinv(double* av, int n);
extern __declspec(dllexport) int vbrmatEVD(double* av, int n, int vect, double* resv);

C++ .def file

; AlgLib2.def defines exports for AlgLib2.dll
LIBRARY Alglib2
EXPORTS
vbrmatinv @1
vbcmatinv @2
vbrmatEVD @3

The screen shots below show the Visual Studio project after compilation of the AlgLib and C++ interface files, and the output from the resulting Excel UDF functions.

C++ Project; click for full size view

Excel Matrix Inversion UDFs

Excel Eigen Value and Eigen Vector UDFs

Time for inversion of a 500 x 500 matrix was about 0.75 seconds, and time for extraction of Eigen values from the same matrix was 3.65 seconds.  Both times were better than 10 times faster than the equivalent VBA functions.