Newton Excel Bach, not (just) an Excel Blog

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:

• ap
• reflections
• creflections
• hqrnd
• matgen
• matinv
• ablasf
• ablas
• trfac
• trlinsolve
• safesolve
• rcond

To get the eigen solver routines to work the following additional files are required:

• evd
• hblas
• hsschur
• mortfac
• rotations
• sblas

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

These functions and the associated AlgLib code may be downloaded from AL-Matrix-xlsb.zip for the 2007 or AL-Matrix-xls.zip for the pre-2007 version.

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

FileNameOnly Function:

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.