Matrix Math with VBA (System of Linear Equations)

Question

I'm looking for a little help performing some matrix mathematics in Excel's VBA. I've read a ton of replies that suggest using the Excel worksheet but I'm hoping to solve this within the VBA code itself.

Although my application is much larger, let's say I have a system of linear equations that I need to solve:

  x1 +   x2 = 8
2*x1 + 4*x2 = 100

This can be solved with the simple matrix formula A*x = B or x = A^(-1) * B where,

A = [1, 1; 2, 4]
B = [8; 100]

If you solve this, you'll find x1 = -34 and x2 = 42. In terms of the matrix, then:

X = [-34; 42]

Using Excel's worksheets alongside its MMULT and MINVERSE functions makes this easy and I've gotten it to work just fine. My problem is I'm needing to do this calculation inside a VBA function. Here's what I'm trying:

Dim A(0 To 1, 0 To 1) As Single
Dim B(0 To 0, 0 To 1) As Single
Dim X(0 To 0, 0 To 1) As Single

A(0, 0) = 1
A(1, 0) = 1
A(0, 1) = 2
A(1, 1) = 4

B(0, 0) = 8
B(0, 1) = 100

X = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(A), B)

Unfortunately, the last line yields a "Compile error: can't assign to array" message. I think it's because I have to specify each element of the array one at a time, but the worksheet functions are array functions.

How do I fix this?

Answer 1

Two things:

The same rule applies as in actual mathematics: B must be a vertical array for matrix multiplication to be possible in your case. Declare it as

Dim B(0 To 1, 0 To 0) As Single

and initialize it accordingly. Also, just declare

Dim X As Variant

since the MMult returns a Variant array. This is what was causing your original error.