Installing AlgLib with Excel VBA

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.
Advertisements
This entry was posted in Arrays, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , , . Bookmark the permalink.

13 Responses to Installing AlgLib with Excel VBA

  1. Pingback: Linking AlgLib c++ to Excel VBA « Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Get your AlgLib routines in VBA « Excel Tips Monster

  3. Pingback: AlgLib Complex Number Functions « Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: ALGLIB Statistics Functions « Newton Excel Bach, not (just) an Excel Blog

  5. marry says:

    Thanks for u work,That’s very helpful.
    I have a issues about :A Function that will multiple 2 generic matrices, however it does not contain any error checking or with the error checking how to do it?

    Like

  6. Rob says:

    I definitely appreciate the code to get the files imported, but it doesn’t seem to be working. I modified it a bit and it looks to work OK hopefully I didn’t mess it up too much. It looks like you never declared the FileNameOnly variable/function. I adjusted the code as such and it seems to run OK. I hope I didn’t mess anything up too much.

    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
    Dim FileNameOnly

    ' Specify path and file spec
    FileSpec = "c:\Users\rwdvc\downloads\alglib-2.6.0.vb6\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
    Application.Modules.Add.InsertFile (FileList(I))
    myfilename = FileList(I)
    myfilename = "Z_" & Mid(myfilename, 1, Len(myfilename) - 4)
    Modules(Modules.Count).Name = myfilename
    Next I
    End Sub

    Like

  7. dougaj4 says:

    Rob – thanks for the comment.

    Filenameonly is a VBA function, but I forgot to include it. I will add it to the post.

    It’s also worth mentioning that once you have the code in a VBA module in an Excel file you can copy a module to a new file simply by dragging it to the other file in the VBE Project Explorer window.

    Like

    • anon says:

      I realize this is a dead thread, but I’m not sure you need the macro to import the .bas files. I was able to get ALGLIB up and running in Excel by simply dragging the .bas files from the ALGLIB directory into the VBA editor.

      Like

  8. dougaj4 says:

    There are no dead threads anon (although some may fade away)!

    I didn’t realise you can drag directly from a directory into the VBA editor, so thanks for the comment. The only drawback seems to be that it comes in as Modulex, rather than the file name.

    Like

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

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

  11. Pingback: what Microsoft think VBA is good for … | Newton Excel Bach, not (just) an Excel Blog

  12. Ryan says:

    Was having some issues, the files would not import (only filenames were imported), added a path variable to the insertfile which fixed the issue. I had the file saved in the same folder and still no luck until I added a path to the filename.

    Thanks very much for your code!

    Sub BatchProcess()

    Dim FileName As String, FileList() As String
    Dim FilePath As String, FileSpec, FileSpec2 As String
    Dim I As Integer
    Dim myfilename As String, FoundFiles As Long

    ‘ Specify path and file spec
    FileSpec = “C:\Users\..\Downloads\alglib-2.6.0.vb6\vb6\src\” & “*.bas”
    FileSpec2 = “C:\Users\..\Downloads\alglib-2.6.0.vb6\vb6\src\”
    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 FileSpec2 & (FileList(I))
    myfilename = “z_” & FileNameOnly(FileList(I))
    myfilename = Mid(myfilename, 1, Len(myfilename) – 4)
    Modules(Modules.Count).Name = myfilename
    Next I
    End Sub

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s