## Matrix math -- Application development

## Matrix math -- Application development

(OP)

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.

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Matrix math -- Application development

=====================================

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

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

Thanks!

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

## RE: Matrix math -- Application development

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

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

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Matrix math -- Application development

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

## CODE

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.

## RE: Matrix math -- Application development

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

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

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

Here is test code

## CODE --> TestCode

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

## CODE --> ResultOfTestCode

Ainv(0,1)=.051+-.142i

Ainv(1,0)=.192+-.067i

Ainv(1,1)=-.05+.118i

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

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!

## RE: Matrix math -- Application development

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

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

## CODE

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

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/

## RE: Matrix math -- Application development

## CODE

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/

## RE: Matrix math -- Application development

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

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.

## RE: Matrix math -- Application development

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:

h

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

h

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/

## RE: Matrix math -- Application development

http://n

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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 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.

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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.

## RE: Matrix math -- Application development

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

## RE: Matrix math -- Application development

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/

## RE: Matrix math -- Application development

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.