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.
Pingback: Linking AlgLib c++ to Excel VBA « Newton Excel Bach, not (just) an Excel Blog
Pingback: Get your AlgLib routines in VBA « Excel Tips Monster
Pingback: AlgLib Complex Number Functions « Newton Excel Bach, not (just) an Excel Blog
Pingback: ALGLIB Statistics Functions « Newton Excel Bach, not (just) an Excel Blog
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?
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
Pingback: Daily Download 13: The ALGLIB maths library and Excel | 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: what Microsoft think VBA is good for … | Newton Excel Bach, not (just) an Excel Blog
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
LikeLike