Matrix Operations in VBA

[wjg]

I would like to take the inverse of a matrix within my VBA code as part
of a sequence of operations. Something like the following:

Dim vMtxA As Variant, vMtxB As Variant, vMtxC() As Variant

vMtxA = Range("B4:E7")
vMtxB = Application.MInverse(vMtxA)

OR

ReDim vMtxC(4,4)

vMtxC = Application.MInverse(vMtxA)


I have not been able to find a way to do this. I would then multiply
the inverse by a RHS vector to get a solution vector that would be used
in subsequent calculations using MMult, etc. Also I have not figgured
out how to add two variant arrays??

vMtxD = vMtxA + vMtxB

 

[Rowan Drummond]

For the MInverse try:

Dim vMtxA As Range, vMtxB As Variant
Set vMtxA = Range("B4:E7")
vMtxB = Application.WorksheetFunction.MInverse(vMtxA)
MsgBox vMtxB(1, 1)

Hope this helps
Rowan

 

[Tom Ogilvy]

there are no built in Matrix/array handling functions in VBA. What you do
have is access to Excel worksheet functions such as you have shown and
Rowand has somewhat duplicated. Rowand might seem to imply that vMtxA must
be a range, but it can be an array as well (as you originally showed).

Sub abcd()
Dim vMtxA As Variant, vMtxB As Variant, vMtxC() As Variant
vMtxA = Range("B4:E7")
vMtxB = Application.MInverse(vMtxA)
Debug.Print LBound(vMtxB, 1), UBound(vMtxB, 1)
Debug.Print LBound(vMtxB, 2), UBound(vMtxB, 2)
For i = LBound(vMtxB, 1) To UBound(vMtxB, 1)
sStr = ""
For j = LBound(vMtxB, 2) To UBound(vMtxB, 2)
sStr = sStr & Format(vMtxB(i, j), "#,000") & ","
Next
Debug.Print sStr
Next
End Sub

works fine.

Array/matrix multiplication would need to be done element by element.
Assignment of arrays/matrices to variables requires a variant (or in xl2000
and later a variant array is also supported).

 

[Tom Ogilvy]

Of course "Rowand" should be "Rowan". My apologies.

 

[wjg]

Rowan and Tom
Thank you very much for your help. I have been exploring this group
and I am finding a variety of implimintations using arrays of doubles,
declared variant arrays, and variants - some with Option Base 1 and
some with 0. I have Exl 2003 and I am looking for a consistent
practice for performing series of matrix manipulations in memory. Here
is my latest try:

Option Explicit
Option Base 1
Private Sub CommandButton2_Click()
Dim vMtxA, vMtxRHS, vMtxAInv, vMtxSol, vMtxT
Dim iRow As Integer, iCol As Integer
vMtxA = Range("A47")
vMtxRHS = Range("F4:F7")
vMtxAInv = Application.WorksheetFunction.MInverse(vMtxA)
ReDim vMtxSol(UBound(vMtxA, 1))
For iRow = 1 To UBound(vMtxA, 1)
vMtxSol(iRow) = 0
For iCol = 1 To UBound(vMtxA, 2)
vMtxSol(iRow) = vMtxSol(iRow) + vMtxAInv(iRow, iCol) _
* vMtxRHS(iCol, 1)
Next iCol
Next iRow
vMtxT = Application.WorksheetFunction.Transpose(vMtxSol)
End Sub

Following is what I see in the Watch Window:

vMtxRHS(1,1) 125, vMtxRHS(2,1) 100, vMtxRHS(3,1) 195, vMtxRHS(4,1)
147 - which is correct but why 2-dimensional?

vMtxSol(1) 19, vMtxSol(2) 7, vMtxSol(3) 12, vMtxSol(4) 15 - which is
correct

vMtxT(1,1) 19, vMtxT(2,1) 7, vMtxT(3,1) 12, vMtxT(4,1) 15 - which
is not the transpose

It seems like when a single column Range is assigned to a variant, the
varient defaults to 2-dimensions with one column.
When the variant vMtxSol is dimensioned using RdDim it has one
dimension as expected.
The transpose didn't appear to work: the column vector remains a column
vector.
I am trying other configurations, but if you have one that is robust
and tested it would be helpful to know your recommendation

 

[Tom Ogilvy]

A column is 2D. It is 1 x n rows and 1 x 1 columns. It is the way a range
is assigned to the variant. You can make it 1D with application.Transpose.

if it is a single row picked up from the sheet, you need to do two
Transpose's to make it 1D.

A 1D array is inherently horizontal from a worksheet perspective, so when
you transposed your 1D array it became 2D vertical - so this is the
transposed array.

 

[Rowan Drummond]

No offense taken...

 

[grenney]

Thank you. This was a help and saved me time.