Matrix math -- Application development
3

Question

Matrix math -- Application development

ePOWEReng (Electrical)

(OP)

14 May 10 17:03

Hello,

I am considering taking some calculations that presently performed using MathCAD and creating an application to perform those calculations instead. The calculations involve creating a matrix of complex numbers and performing some matrix math operations to arrive at the solution. The complex matrices will be up to 200x200 in size and matrix inversion will be required. My question is what programming language and development platform would make be the easiest to implement a program like this in? Are there libraries out there for performing complex matrix inversion? I was considering using the microsoft visual platform...

Thanks for your input and suggestions.

electricpete (Electrical) · Answer 1 · 2010-05-14 17:56:09

In VBA, you have only arrays, not matrices. There is no such thing as array multiplication. If you want to matrix multiply in vba you have to build a a routine to for it. You could put your vba variable into a range and use excel's mmult... but that is limited to real numbers.

Now there's the question of complex arithmetic. VBA has nothing. It can access excel's functions like imabs, immult. The tricky part you might never have guessed... the vba variables operated on by application.imabs and application.immult will have to be strings. That was surprising to me anyway. But if you inspect a spreadsheet with complex variables, you'll see they are also stored as strings.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

DanStro (Mechanical) · Answer 2 · 2010-05-14 17:59:30

There are a couple of decent numeric (including matrix math) libraries available for Java. But from what I've seen there are many more for C/C++ and I think maybe even more for Fortran.

So I guess pick your poison. If there is a particular language that you know (or want to learn) other than VBA you can probably find something, even if it is not too complete.

IRstuff (Aerospace) · Answer 3 · 2010-05-14 18:03:42

Why not just use Matlab? or Octave, or Scilab? the latter two being free.

Unless you intend to sell the application, I don't see the utility or necessity of inventing a routine, debugging, etc., when there are already tools that do the job.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

ePOWEReng (Electrical) · Answer 4 · 2010-05-14 18:51:58

The goal is to have an application that can be run on any PC without installing any other software except the application itself so I don't want to use matlab or similar software. I was hoping to find programming libraries for building matrices, complex numbers and performing matrix math operations so I don't have to develop those from scratch.

I found some libraries from the company below. Does have experience using this or something similar who could provide feedback or review or other recommendations?

www.extremeoptimization.com/

Thanks.

IRstuff (Aerospace) · Answer 5 · 2010-05-14 19:50:50

Matlab has runtime modules that do not require the entire Matlab installation, I think...

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

electricpete (Electrical) · Answer 6 · 2010-05-14 20:42:08

What is .NET?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 7 · 2010-05-14 20:44:04

ok, I can answer that.
http://en.wikipedia.org/wiki/.NET_Framework

Next question... does it run on anyone's PC. Looks like you have to install something to run .NET programs.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IRstuff (Aerospace) · Answer 8 · 2010-05-14 20:53:25

If you use the .NET framework components, then the target computer will need .NET of the version you used installed on it.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

IDS (Civil/Environmental) · Answer 9 · 2010-05-14 22:06:51

I think ALGLIB is the resource you are looking for.

http://www.alglib.net/matrixops/inv.php

Note that all their routines are available in VB as well as various flavours of C and Pascal, and they have routines for complex numbers and all the usual matrix operations.

If it was me I'd do it in Excel VBA and then look at compiling any routines that were resulting in unsatisfactory performance (if any), or compiling the whole thing if security was an issue.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 10 · 2010-05-14 22:30:11

That looks very promising Doug. I have been looking for vba matrix tools for awhile.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

ePOWEReng (Electrical) · Answer 11 · 2010-05-14 22:45:30

Thanks for the link doug. It looks like they have code for finding the determinant of complex matrix, but I didn't see anything for inversion of a complex matrix. Any ideas where I might find code for that in VBA?

Thanks!

electricpete (Electrical) · Answer 12 · 2010-05-14 23:03:58

I inspected the module matinv and it uses the complex type (defined in ap.bas). I'm pretty sure this can do what you want. There is some setup to do.

For one thing ap.bas has to be available. I cut/pasted it into a module and then I had access to the complex type and associated basic functions.

I'm a little bit confused about matinv. The matinv.bas file has function matinvreport, but no funciton matinv

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 13 · 2010-05-14 23:13:14

Looks like cmatrixinverse is what you need.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

ePOWEReng (Electrical) · Answer 14 · 2010-05-14 23:15:04

RE: Matrix math -- Application development

ePOWEReng (Electrical)

(OP)

14 May 10 23:15

Great! Thanks for the help.

electricpete (Electrical) · Answer 15 · 2010-05-14 23:37:21

You're welcome. But, still seems a challenge to set this thing up.

I copied ap.bas into one module.
I copied matinv.bas into another module.
In a 3rd module I tried to create my own test code:

CODE

Sub test1()

Dim a11 As Complex, a12 As Complex, a21 As Complex, a13 As Complex
Dim A(1 To 2, 1 To 2) As Complex
Dim XX As MatInvReport
A(1, 1).X = 5
A(1, 1).Y = 2
A(1, 2).X = 6
A(1, 2).Y = 2
A(2, 1).X = 3
A(2, 1).Y = 9
A(2, 2).X = 1
A(2, 2).Y = 2

' ainv = CMatrixInverse(A, 2)
' Call CMatrixInverse(A, 2, 0, Z)
Call CMatrixInverse(A, 2, 0, XX)

Stop
End Sub

The result of trying to run this sub is an error message within module CMatrixInverse:
Sub or Function Not defined: CMatrixLU

There is a lot of goodies in this package that I'd like to have, but it also requires some additional setup I think that I can't quite figure out.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 16 · 2010-05-15 00:01:19

Again, I suspect there is some setup I am missing. I posted a question here:
http://forum.alglib.net/viewtopic.php?f=2&t=7

Will post back here if any response appears.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 17 · 2010-05-15 00:13:21

OK, I figured out that I need trfac.bas loaded into a module also in order to have CMatrixLU. Perhaps there is some way to tell vba where these bas files are so it can find whatever it needs?

Even after I loaded trfac.bas into a module, I still have a problem.
Runtime Error 9 - subscript out of range.

This error is associated with the line
MX = MaxReal(MX, AbsComplex(A(I, J)))
located within module CMatrixPLU of module trfac

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 18 · 2010-05-15 07:31:54

Electricpete - the arrays need to be base 0.

I see there is a reply to your question on their forum.

I got it to run with no reported errors after installing all the files listed (and turning off option explicit!), but it returns the original matrix!

I'm still looking.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 19 · 2010-05-15 08:22:07

D'oh

I was inverting a matrix that had been inverted with the Excel MINVERSE function, so I was getting the original matrix back!

Seems to be working ok with real numbers, I'll have a look at the complex routine now.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 20 · 2010-05-15 09:02:38

Complex matrix inversion seems OK.

Does anyone have a simple numerical example they can post for testing purposes?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 21 · 2010-05-15 10:57:26

Quote:
Does anyone have a simple numerical example they can post for testing purposes?

The solution for the example I posted above can be obtained in Matlab as follows

CODE

»  A=[5+2*i, 6+2*i; 3+8*i, 1+2*i]

A =

   5.0000 + 2.0000i   6.0000 + 2.0000i
   3.0000 + 8.0000i   1.0000 + 2.0000i

» inv(A)

ans =

  -0.0482 + 0.0227i   0.0510 - 0.1416i
   0.1921 - 0.0669i  -0.0504 + 0.1178i

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 22 · 2010-05-15 11:00:09

What I meant to say - the solution is the last thing listed after "ans ="...

I still haven't gotten it to work...

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 23 · 2010-05-15 11:30:03

After loading the required libraries, I still get the same error that I described 15 May 10 0:13.

It is looking at a matrix A (his A, not mine) and accessing element A(I,J) where I=0 and J=0 at the time of the call. The matrix starts with element 1,1.

Maybe I need to add Option Base 0 in front of his modules?
I am using Excel 2000

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 24 · 2010-05-15 11:44:35

Thanks Doug. I see now you have suggested my matrix needs to be base zero. I'd vote you a LPS, but I already voted you one in this thread.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 25 · 2010-05-15 12:12:46

After heeding Doug's advice, it does in fact give same answer as Matlab.

Here is test code

CODE --> TestCode

Sub test1()
Dim A1(0 To 1, 0 To 1) As Complex
Dim XX As MatInvReport

' Initialize Matrix
A1(0, 0).X = 5
A1(0, 0).Y = 2
A1(0, 1).X = 6
A1(0, 1).Y = 2
A1(1, 0).X = 3
A1(1, 0).Y = 8
A1(1, 1).X = 1
A1(1, 1).Y = 2

Call CMatrixInverse(A1, 2, 0, XX)
Debug.Print "Ainv(0,0)=" & Format(A1(0, 0).X, "#.###") & "+" & Format(A1(0, 0).Y, "#.###") & "i"
Debug.Print "Ainv(0,1)=" & Format(A1(0, 1).X, "#.###") & "+" & Format(A1(0, 1).Y, "#.###") & "i"
Debug.Print "Ainv(1,0)=" & Format(A1(1, 0).X, "#.###") & "+" & Format(A1(1, 0).Y, "#.###") & "i"
Debug.Print "Ainv(1,1)=" & Format(A1(1, 1).X, "#.###") & "+" & Format(A1(1, 1).Y, "#.###") & "i"

End Sub

Here is result

CODE --> ResultOfTestCode

Ainv(0,0)=-.048+.023i
Ainv(0,1)=.051+-.142i
Ainv(1,0)=.192+-.067i
Ainv(1,1)=-.05+.118i

So it works, but there are two minor inconveniences:
1 - we need to make sure all our matrices are dimensioned as base 0.
2 - the subroutine overwrites the matrix that we passed with it's inverse. Need to be careful how you call it. I guess I'd be inclined to create a function that calls the subroutine and returns the value without overwriting the argument. Unless there is some easier way or something I'm missing?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 26 · 2010-05-15 13:23:51

There is a challenge in using this that you have to import so many supporting files.  Especially if you'd like to access a broad range of functions.  Note there is no clear map for dependencies among files... as seen above it was not easy to figure out trfac.bas was required to support subroutine CMatrixInverse which itself was located in matinv.bas.  And perhaps some others required (I loaded all the ones he said in the post on the alglib forum).

So it would be nice to be able to load them all once, and then not have to ever do it again.  And it would be very ugly to have them cluttering up the module list (such as if I just try to use the same xls file that I loaded the alglib modules into).

To me it seems like perfect application for an excel add-in.

So after I imported, I saved my file with required modules as xla.  As attached.

Then I closed excel, created a new spreadsheet, went to add-ins menu and added my just-created add-in.  Then went to vba window... I could see the new add-in had showed up as a Project.  But when I tried to run my test code again, it does not recognize the Complex type.  Seems like some kind of scoping problem.  The add-in has a module with a public declariation of Complex datatype.  Why can't it be seen in other Projects?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

http://files.engineering.com/getfile.aspx?folder=b4640786-a478-49d9-90a6-fc

electricpete (Electrical) · Answer 27 · 2010-05-15 13:35:32

I guess public gets you as far as the project level, but not beyond that. I'll have to study up a little on add-ins and scoping. Seems pretty useless if you can't access public declarations and public from an add-in.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

ePOWEReng (Electrical) · Answer 28 · 2010-05-15 13:52:26

Thanks Doug and Electric pete. I imported all the correct modules and it seems to be working.

Good points on use of the function too electric pete. I think as long as I keep that in mind it shouldn't be difficult to work around.

Thanks!

electricpete (Electrical) · Answer 29 · 2010-05-15 17:57:09

I found a way to automatically insert all 101 bas files into modules. The code was adapted from Walkenbach.

1 - Create a new worksheet, insert a module, and put the code below into it.

2 - Put the 101 bas files into a directory and edit the FilePath below FROM "C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\" TO whatever your directory is.

3 - Run the sub BatchProcess

CODE --> AdaptedFromWalkenbach

Sub BatchProcess()
Dim FS As FileSearch
Dim FilePath As String, FileSpec As String
Dim I As Integer
Dim myfilename As String

' Specify path and file spec
FilePath = "C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\"
FileSpec = "*.bas"
' Create a FileSearch object
Set FS = Application.FileSearch
With FS
.LookIn = FilePath
.FileName = FileSpec
.Execute
' Exit if no files are found
If .FoundFiles.Count = 0 Then
MsgBox "No files were found"
Exit Sub
End If
End With
' Loop through the files and process them
For I = 1 To FS.FoundFiles.Count
Stop
Application.Modules.Add.InsertFile (FS.FoundFiles(I))
myfilename = "z_" & FileNameOnly(FS.FoundFiles(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

One small downside, the file is already 5MB, and there is no data in it yet (just code).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 30 · 2010-05-15 17:59:15

I forgot to mention one thing - I put a z_ in front of every filename. That way all of the library modules will sort to the end of the list and it won't be so cumbersome to find your own modules within the module list.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 31 · 2010-05-15 18:00:53

Last post - here is the indented version of the code. Easier on the eyes.

CODE

Sub BatchProcess()
Dim FS As FileSearch
Dim FilePath As String, FileSpec As String
Dim I As Integer
Dim myfilename As String

    ' Specify path and file spec
    FilePath = "C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\"
    FileSpec = "*.bas"
    ' Create a FileSearch object
    Set FS = Application.FileSearch
    With FS
        .LookIn = FilePath
        .FileName = FileSpec
        .Execute
        ' Exit if no files are found
        If .FoundFiles.Count = 0 Then
            MsgBox "No files were found"
            Exit Sub
        End If
    End With
    ' Loop through the files and process them
    For I = 1 To FS.FoundFiles.Count
        Stop
        Application.Modules.Add.InsertFile (FS.FoundFiles(I))
        myfilename = "z_" & FileNameOnly(FS.FoundFiles(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

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 32 · 2010-05-15 18:29:26

Quote:
My question is what programming language and development platform would make be the easiest to implement a program like this in?

I am partial to excel vba for a number of reasons: mostly that it's portable.

BUT, if you're looking for "easy to program" matrix operations, it's clearly not here in vba (nowhere near as easy/compact as Matlab). Since each multiplication is a function call, you will end up with a lot of nesting or have to break down matrix equations into baby steps. As a somewhat frustrating example I have encountered... you can't even use A=B where A is dimensioned as NxN and B is NxN. You have to write a routine to do it on an element by element basis, or else A has to be a new variable not yet dimensioned. So to accomplish A=B*C you end up something like:
Call Mequals(A, Mmultiply(B,C))
where Mequals is your element by element assignment routine that puts the values from the 2nd argument into the 1st on an element by element basis. Mmultiply would be a function. But you can't say A=Mmultiply(B,C) if A is already dimensioned (even though the dimensions are compatible).

There may be some easier workaround, but that's what I came up with.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 33 · 2010-05-15 19:55:03

electricpete - thanks for the code for inserting the code automatically, I was just about to go and do it manually.

I verified the complex inversion routines with your example and got the same answer, so thanks for that as well.

I wrote a UDF to convert the arrays to a suitable form for the AlgLib routines. Here it is:

CODE

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, Tmp 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
            Tmp.X = A(I, J)
            Tmp.Y = A(I, J + 1)
            A2(I - 1, K) = Tmp
        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
            Tmp = A2(I - 1, J - 1)
            A(I, J * 2 - 1) = Tmp.X
            A(I, J * 2) = Tmp.Y
        Next J
    Next I

    CMatInv = A
End Function

I'll comment on your last post later, but I have to go and do some .bas importing right now :)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 34 · 2010-05-15 20:34:14

Apparently the filesearch object no longer works in Excel 2007. The code below works in 2007, but I had to save the workbook in the same directory as the .bas files.

CODE

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

If you are using XL2007 it's worth saving as an xlsb file. The file size is reduced to 2.2 MB and it saves very much more quickly than an xls file.

Thanks again to electricpete for posting the original version.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 35 · 2010-05-16 12:36:46

I like your idea of a front-end function for CMatrixInverse to accomplish two things:
1 - Provide a function call rather than a sub which stuffs Ainverse into A.
2 - Make sure the indexing starts as zero.

I think I'd be inclined to store my vba complex variables as user-defined type "complex" (defined in module ap) or arrays of NxN complex  variables (like the example I posted), rather than a Nx2N array of scalars that gets decoded to/from complex in the function call.     A conversion does has to be made from vba format to format compatible with outside world (for example spreadsheet), my preference would be to do that conversion as part of input/output routines.  Just a personal preference.

Note Alglib provides a few functions for manipluating udt complex variables directly.  They roughly correspond to the same routines provided in the  excel analysis tookpak.   Except one that is missing is something to generate the angle of a complex number (like imargument).  But it would be easy to create that missing function... the hardest part (most important part) would be keeping organized in storing/tracking these extra functions like the angle funciton or the front-end for Cmatrix or the input/output functions.

Regarding loading those 101 modules, you can see here that the 101 are divided into "packages" with no more than 20 or so modules in a package.:
http://www.alglib.net/translator/man/manual.vb6.html#alglib_main
I am pretty sure that in order to access a function or subroutine from a package, it would be sufficient to load just the modules from that package, along with the special module "ap".  That would cut down on size of the file.  I'm going to split the source files into separate directories on my harddrive (one directory per package) for the purpose of making it easier to import only modules needed by a package.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 36 · 2010-05-16 18:52:53

On the issue of using functions in an add-in from VBA, you need to:

1) Rename the add-in VBA project to something other than "VBAProject"
2) Create a reference to the project, using the Tools-References menu.

See link for more details:
http://www.exceltip.com/st/How_to_Use_Your_Excel_Add-In_Functions_in_VBA/630.html

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 37 · 2010-05-16 19:34:33

I mentioned earlier that I had to switch off Option Explicit to get the matrix inversion sub to run. For some reason it is now working, even if Option Explicit is set, but if I run Debug-Compile VBA Project I get a load of errors.

It seems that it is only AP.bas that has the problem, so I have gone through and added dim statements for all the undeclared variables. I'll post the updated file when I've tested it more thoroughly.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 38 · 2010-05-20 06:28:05

I have summarised the procedures for installing the AlgLib code, and posted some sample functions (matrix inversion and eigen values) here:

http://newtonexcelbach.wordpress.com/2010/05/20/installing-alglib-with-excel-vba/

which might be useful for anyone looking at the code and wondering where to start.

I will also be posting some examples of linking to c++ dlls from VBA in the near future.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 39 · 2010-05-22 09:19:15

Procedures for linking AlgLib C++ routines to Excel VBA, including example matrix inversion and eigen value functions here:

http://newtonexcelbach.wordpress.com/2010/05/22/linking-alglib-c-to-excel-vba/

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 40 · 2010-05-23 15:24:23

Thanks Doug.  That is some useful info for anyone getting started with Alglib in vba.

I will use some of your ideas for interface function.  I like the idea of allowing spreadsheet range as input whose .value property returns a variant matrix.

One thing about Alglib... there are a variety of routines that perform similar functions with similar names.  To avoid adding to the confusion, I think I would just name the interface functioms something like aif_xxx where xxx is the exact alglib routine number.  (AIF for Alglib Interface Function).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 41 · 2010-05-23 15:45:20

Thanks Doug.  That is some useful info for anyone getting started with Alglib in vba.

I will use some of your ideas for interface function.  I like the idea of allowing spreadsheet range as input whose .value property returns a variant matrix.

One thing about Alglib... there are a variety of routines that perform similar functions with similar names.  To avoid increasing the complexity of an already-confusing naming scheme,  I think I would just name the interface functioms something like aif_xxx where xxx is the exact alglib routine number.  (AIF for Alglib Interface Function).

For matrices in vba, there are a few routines that have to be built.
multiplication, inversion, and determinant, unless you use the worksheet functions which have some limitations.

multiplication of a matrix by a scalar.

addition of matrices

negation of matrices.

retrieving portion of a matrix (like row 2-4, column 3-5) or replacing portion fo a matrix.

There is also the problem of assignment that I mentioned.   You can get around that one using the variant matrix type.  There are pro's and con's there.  One downside is that will slow calcualtions way down. Also you will be limited in what you can do for error checking.  In contrast if you use an assignment routine, you can error check the size of the matrices.

There are no insurmountable obstacles for doing complicated matrix manipulations in vba similar to Matlab. Just a lot of bookkeeping details.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 42 · 2010-05-23 15:46:51

Sorry, I didn't mean to include inversion and determinant as routines the user would build. I think we can get them from Alglib.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 43 · 2010-05-24 00:23:09

electricpete - thanks for the feedback.  I agree with the comment about the procedure names being potentially confusing if done on an ad-hoc basis (as I have been up to now).  The only problem I have with just sticking an AIF_ on the front is that I don't like long function names (especially for UDFs), but I agree something systematic needs to be done.

With regard to assiging spreadsheet ranges to variant arrays - a few points:

It's better to use the .value2 property than .value because that has less memory overhead and is significantly quicker.  I think you lose the currency formatting information (which I never use anyway).

It's true that operating on double arrays is much quicker than working with variant arrays, but if you are transferring data to the Alglib routines you need to convert them to a base 0 array of doubles anyway, so you will be doing all the processing on doubles, not variants.

I wasn't sure what you meant about the error checking.  You can check the size of a variant array just like any other array.

I've been playing with matrix multiplication routines.  I was a bit surprised to find that using worksheetfunction.mmult was much faster than any VBA routine I could come up with.  In XL2007 the maximum matrix size is much bigger than in earlier versions.  I don't know what the maximum size is, but it's more than 500x500.

The AlgLib routines have matrix multiplication buried away in the ABLAS module (RMatrixGEMM and CMatrixGEMM).  They have a whole long list of inputs (allowing scalar matrices, and working with sub matrices) but it is quite easy to write a front end just requiring the two matrices to be multiplied as input.  When I get time I will compile the c++ version and see how it compares with the worksheetfunction.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 44 · 2010-05-25 13:21:46

Good comments.

Regarding error checking – if you plug the wrong type of data into a variant, there will be no error.  That's probably not a big problem, just depends on the situation and your preferences.

I did find that we can do block assignment (using =) for arrays of doubles if we are careful about the syntax as below.

I did  a timing test similar to what you described for matrix multiplication.

Results were:
mmult takes 0.34 sec
vba routine to multiply doubles time takes 1.42 sec
vba routine to multiply variants time takes 3.08

So there is definitely a penalty for using variants if you will be doing a lot of calculations with them (not relevant if you are just passing straight to Alglib... depends what you're doing).   I agree it is interesting that excel's mmult is so much faster.... I assume it is somehow coded at a lower level that doesn't have so much book-keeping on the indeces.

Here is the code that I used to run the timing test:

CODE

Option Explicit
Option Base 1

Sub timetest()  '   TIMING TEST:
' OBJECTIVE: Compute C = A*B 50 times using 3 different methods

' Miscellaneous Variables:
Dim counter As Integer ' iteration counter
Const iters = 50 ' iteration limit
Const matsize = 65 ' matrix size
Dim tstart As Double, telapsed As Double  'timing variables

' define/initialize variant matrices A, B, C
Dim av As Variant
Dim bv As Variant
Dim cv As Variant
av = eye(matsize)
bv = eye(matsize)

' define/initialize double matrices A, B, C
Dim ad() As Double
Dim bd() As Double
Dim cd() As Double
ad = eye(matsize)
bd = eye(matsize)

' Time test using excel Mmult:
tstart = Timer
For counter = 1 To iters
cv = Application.WorksheetFunction.MMult(ad, bd) ' didn't work with cd =...
Next counter
telapsed = Timer - tstart
Debug.Print "excel mmult time is " + CStr(telapsed)

' Time test using vba function with doubles
tstart = Timer
For counter = 1 To iters
cd = ma_multdoub(ad, bd)
Next counter
telapsed = Timer - tstart
Debug.Print "multiply doubles time is " + CStr(telapsed)

' Time test using vba function with variants
tstart = Timer
For counter = 1 To iters
cv = ma_multvar(av, bv)
Next counter
telapsed = Timer - tstart
Debug.Print "multiply variants time is " + CStr(telapsed)

End Sub

Function ma_multvar(a, b) ' vba function to multiply using variants

'Error check on dimensions:
If UBound(a, 2) <> UBound(b, 1) Then MsgBox ("error in input dimensions ma_multvar")

ReDim output(1 To UBound(a, 1), 1 To UBound(b, 2)) As Double
Dim row As Integer, col As Integer, counter As Integer

For row = 1 To UBound(a, 1)
  For col = 1 To UBound(b, 2)
    For counter = 1 To UBound(a, 2)
      output(row, col) = output(row, col) + a(row, counter) * b(counter, col)
    Next counter
  Next col
Next row

ma_multvar = output
End Function

Function ma_multdoub(a() As Double, b() As Double) As Double()  ' Multiply doubles
' vba function to multiply using variants

' Error check on dimensions:
If UBound(a, 2) <> UBound(b, 1) Then MsgBox ("error in input dimensions ma_multvar")

ReDim output(1 To UBound(a, 1), 1 To UBound(b, 2)) As Double
Dim row As Integer, col As Integer, counter As Integer

For row = 1 To UBound(a, 1)
  For col = 1 To UBound(b, 2)
    For counter = 1 To UBound(a, 2)
      output(row, col) = output(row, col) + a(row, counter) * b(counter, col)
    Next counter
  Next col
Next row

ma_multdoub = output
End Function

Function eye(n) As Double() ' Return identify matrix "I"

ReDim tempeye(1 To n, 1 To n) As Double
Dim row As Integer, col As Integer

For row = 1 To n
  For col = 1 To n
    If row = col Then
        tempeye(row, col) = 1
    Else
        tempeye(row, col) = 0
    End If
  Next col
Next row

eye = tempeye
End Function

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 45 · 2010-05-25 22:08:55

electricpete - a couple of points on your code:

The point of using a variant array is in getting data from the spreadsheet into VBA using:

VBAarray = Range("rangename").value2

If VBAarray is declared as a double this won't work.
If the array is going to be used in something like matrix multiplication or inversion, then it is well worthwhile to copy it into a double array with two nested for-next loops. In the case of the AlgLib routines this also allows you to change to base zero, and assign the values to one of the Alglib data types where appropriate.

A minor point on your counters, it's actually more efficient to declare them as longs rather than integers, even if they will always be within the integer range, because VBA converts integers into longs anyway. Or so I'm told, I've never actually checked it.

I have now compiled the AlgLib C++ matrix multiplication routine, and got the following results multiplying two 250 x 250 matrices once:

C++ function: 0.06 sec
worksheetfunction.Mmult with variant array: 0.51 sec
worksheetfunction.Mmult with double array: 0.51 sec
worksheetfunction.Mmult with range object: 0.48 sec
VBA function with double array: 1.6 sec
VBA function with variant array: 4.5 sec

I've found the same with matrix inversion etc, the C++ routines are of the order of 10 times faster than the equivalent Excel built in function, and about 30 times faster than a VBA function operating on doubles.

So the question is not why the built in functions are fast, it is why are they so slow?

BTW, the compiled functions are also available to use on the worksheet, and still have much the same speed advantage.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 46 · 2010-05-25 23:25:14

Interesting about the long vs integer. I tried my code above with long and the time did indeed decrease 10% - 20%.

Can you post the code that you used to compare variant multiplication and double multiplication? I don't understand why your results would show identical performance when mine showed more than a factor of 2 difference.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 47 · 2010-05-25 23:38:56

Quote (electricpete):
Can you post the code that you used to compare variant multiplication and double multiplication? I don't understand why your results would show identical performance when mine showed more than a factor of 2 difference.

Never mind - I see you were just testing these variable types with mmult.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IDS (Civil/Environmental) · Answer 48 · 2010-05-26 00:38:36

Yes, no question that variants are way slower than doubles for calculation intensive applications.

The only question is whether it is worth the time and effort of copying the variant array (as delivered from the spreadsheet) into a double array. It seems that the answer for any but the smallest matrix operation is yes.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 49 · 2010-05-26 08:57:53

One other small advantage of double ( ) over variant is it will improve the self-documenting nature of the code – it's easier to figure out what a variable represents when you know it's type. This applies both when reviewing he code and when typing in a call for a user defined function ( the interactive display editor prompts you with names and data types for each argument).

I plan on building a small library of routines for real matrices:  matrix multiplication, scalar multiplication, addition, negation, transpose,  extract submatrix, insert submatrix, determinant, inverse, input/output etc.  (some are just calls to other routines such as AlgLib).   Ideally they are standardized in terms of the forrm that they accept and return real matrices, and I'll use double() for that purpose.  The function names will all begin with prefix ma_.. for matrix.  Then I'll build an analogous library of routines for complex matrices with same  names, but prefix cm_ for complex matrix.  Changing the type declarations in modules, function argument declarations, and function return declaration would just involve changing double to complex (in contrast if we used variant, there is a fundamentally different structure that is not as easily adaptable)

Sorry – didn't mean to beat a dead horse on choice of type – I am sure I'm not telling you anything new about programming in general... just some thoughts about my preferences and where I think I'm heading.

Separate subject – it is revealing to see that C++ compiled routines work so much faster.  Unfortunately I haven't worked with that at all so I don't think I can easily take advantage of it.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

Come Join Us!

Posting Guidelines

Matrix math -- Application development 3