In a previous post I linked to the AlgLib site which has an extensive range of mathematical routines available in VBA, c/c++ and pascal. I have recently been experimenting with these (in reponse to a thread at the Eng-Tips forum), and will be summarising some procedures to simplify the process. This post will cover installing the VBA routines, and a future post will look at compiling the c++ routines as a dll, and linking to this from VBA. As well as the Eng-Tips discussion there is further useful information at the AlgLib Forum.
The main problem with installing the VBA routines is the number of them (102 separate .bas files), and the fact that they are interlinked, so that installing 1 VBA routine may require 10 or more of the .bas files (which are best installed each in a separate module, since some are quite large). For instance to install the basic routines for inversion of real or complex matrices (rmatrixinverse and cmatrixinverse) requires the following modules:
To get the eigen solver routines to work the following additional files are required:
Having established which .bas files are required for the routines you want to install the procedure then is:
- Import each .bas file into a separate module
- Write a simple interface routine to convert your data into the form required by the AlgLib routines.
The procedure to import the .bas files is (but see the next section for a macro that automates the operation)
- Open the Visual Basic Editor (VBE) – press Alt-F11
- Make sure that you have the right project selected in the Project Explorer window on the left.
- Right-click Insert-Module
- Right-click the new module Import-File and select the appropriate .bas file
- In the Properties window (with the new module selected) click on the name box and change the module name from Modulex to the name of the .bas file. If the Properties window is not open then open it with View-Properties Window, or press F4
Four example interface routines are shown below:
Invert a general matrix (calls RMatrixInverse)
Function RMatInv(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, INFO As Long, Rep As MatInvReport If TypeName(a) = "Range" Then a = a.Value2 M = UBound(a) N = UBound(a, 2) ReDim A2(0 To M - 1, 0 To N - 1) K = 0 For i = 1 To M For J = 1 To N A2(i - 1, J - 1) = a(i, J) Next J Next i Call RMatrixInverse(A2, M, INFO, Rep) RMatInv = A2 End Function
Note that the data in the worksheet range is converted into a 2D variant array (base 1) with the statement a = a.Value2, and this must then be copied into a base 0 2d double array for use in the AlgLib routine.
Invert a complex matrix (calls CMatrixInverse)
Function CMatInv(a As Variant) As Variant Dim N As Long, M As Long Dim A2() As Complex, i As Long, J As Long, K As Long, INFO As Long Dim Rep As MatInvReport, Tmpc As Complex a = a.Value2 M = UBound(a) N = UBound(a, 2) ReDim A2(0 To M - 1, 0 To N / 2 - 1) ' Convert value pairs to Complex type and write to base zero array K = 0 For i = 1 To M For J = 1 To N - 1 Step 2 K = (J + 1) / 2 - 1 Tmpc.X = a(i, J) Tmpc.Y = a(i, J + 1) A2(i - 1, K) = Tmpc Next J Next i Call CMatrixInverse(A2, M, INFO, Rep) ' Convert Complex results back to pairs of doubles For i = 1 To M For J = 1 To N / 2 Tmpc = A2(i - 1, J - 1) a(i, J * 2 - 1) = Tmpc.X a(i, J * 2) = Tmpc.Y Next J Next i CMatInv = a End Function
In this code the components of the complex numbers are stored in adjacent cells, rather than using the Excel (string based) complex numbers.
Find eigenvalues and eigenvectors of a general matrix (calls RMatrixEVD)
Function EigenvR(a As Variant, Optional Vect As Long = 0) As Variant Dim N As Long, M As Long, D() As Double, Z() As Double, Res As Boolean, ResA() As Double Dim A2() As Double, i As Long, J As Long, K As Long, wr() As Double, wi() As Double, vl() As Double, vr() As Double If TypeName(a) = "Range" Then a = a.Value2 M = UBound(a) N = UBound(a, 2) ReDim A2(0 To M - 1, 0 To N - 1) K = 0 For i = 1 To M For J = 1 To N A2(i - 1, J - 1) = a(i, J) Next J Next i Res = RMatrixEVD(A2, M, Vect, wr, wi, vl, vr) If Res = True Then If Vect = 0 Then ReDim ResA(1 To 2, 1 To M) Else ReDim ResA(1 To M * 2 + 2, 1 To M) For i = 0 To M - 1 ResA(1, i + 1) = wr(i) ResA(2, i + 1) = wi(i) Next i If Vect = 0 Then EigenvR = ResA Exit Function End If If Vect = 1 Or Vect = 3 Then For i = 0 To M - 1 For J = 0 To M - 1 ResA(i + 3, J + 1) = vr(i, J) Next J Next i End If If Vect = 2 Or Vect = 3 Then For i = 0 To M - 1 For J = 0 To M - 1 ResA(i + M + 3, J + 1) = vl(i, J) Next J Next i End If EigenvR = ResA Else EigenvR = "Did not converge" End If End Function
Find eigenvalues and eigenvectors of a symmetric matrix (calls SMatrixEVD)
Function EigenvS(a As Variant, Optional Vect As Long = 0, Optional IsUpper As Boolean = True) As Variant Dim N As Long, M As Long, D() As Double, D2() As Double, Z() As Double Dim A2() As Double, i As Long, J As Long, K As Long, Res As Boolean If TypeName(a) = "Range" Then a = a.Value2 M = UBound(a) N = UBound(a, 2) ReDim A2(0 To M - 1, 0 To N - 1) K = 0 For i = 1 To M For J = 1 To N A2(i - 1, J - 1) = a(i, J) Next J Next i Res = SMatrixEVD(A2, M, Vect, IsUpper, D, Z) ReDim D2(1 To M + 1, 1 To M) If Vect = 0 Then EigenvS = D Else For J = 0 To M - 1 D2(1, J + 1) = D(J) Next J For i = 0 To UBound(Z) For J = 0 To M - 1 D2(i + 2, J + 1) = Z(i, J) Next J Next i EigenvS = D2 End If End Function
The need to determine all the routines required for a given function may be avoided by importing all 102 of the AlgLib .bas files into separate modules in a single worksheet file. This tedious process is made easy by the code below, which will import all of the .bas files into the specified directory (copy the BatchProcess routine and the FileNameOnly function to a new code module in the file where you want to import the ALGLIB routines) :
Sub BatchProcess() Dim FileName As String, FileList() As String Dim FilePath As String, FileSpec As String Dim I As Integer Dim myfilename As String, FoundFiles As Long ' Specify path and file spec FileSpec = "d:\Users\Doug\Documents\SPREAD\AlgLib\vb6\src\" & "*.bas" FileName = dir(FileSpec) ' Exit if no files are found If FileName <> "" Then FoundFiles = 1 ReDim Preserve FileList(1 To FoundFiles) FileList(FoundFiles) = FileName Else Exit Sub End If Do FileName = dir If FileName = "" Then Exit Do FoundFiles = FoundFiles + 1 ReDim Preserve FileList(1 To FoundFiles) FileList(FoundFiles) = FileName Loop ' Loop through the files and process them For I = 1 To FoundFiles Stop Application.Modules.Add.InsertFile (FileList(I)) myfilename = "z_" & FileNameOnly(FileList(I)) myfilename = Mid(myfilename, 1, Len(myfilename) - 4) Modules(Modules.Count).Name = myfilename Next I End Sub
Public Function FileNameOnly(pname) As String ' Returns the filename from a path/filename string Dim I As Integer, length As Integer, Temp As String length = Len(pname) Temp = "" For I = length To 1 Step -1 If Mid(pname, I, 1) = Application.PathSeparator Then FileNameOnly = Temp Exit Function End If Temp = Mid(pname, I, 1) & Temp Next I FileNameOnly = pname End Function
The file containing these two macros should be copied to the same directory as the .bas files, before running it.
One drawback with this approach is that it produces a very large file; 5.3 MB as an xls file, or 2.3 MB as an xlsb file. In order to avoid making multiple copies of these large files they can be saved as an add-in file (xla or xlam). If this is done any UDFs in the file will automatically be available from the worksheet whenever it is loaded, but to make the functions available from VBA in other files the following procedure is necessary:
- In the VBE properties window change the project name from VBAProject to anything else (e.g. AlgLibVBA)
- In Tools-References select the check box opposite the new project name.
- The VBA in the add-in file will now be available from other worksheets whenever it is loaded.