How to properly define a matrix in VBA?

Question

Hi I want to be able to enter and multiply two matrices using a macro.

These dimensions of the matrices will change but for simplicity let's assume they are both 2x2 matrices.

So how do I go about doing this so far I have:

Set X = Range("A1", Range("A1").End(xlDown).End(xlToRight))    
Set Y = Range("E1", Range("E1").End(xlDown).End(xlToRight))
Range("W1", Range("W1").Offset(2, 2)).Select
Range("W1").Value = "{=MMULT(X,Y)}"

Clearly this is wrong but I don't know how to fix this.

Any help?

Thanks!

Possible duplicate of [How can I insert variable into formula in VBA](https://stackoverflow.com/q/42503316/11683) — GSerg, Nov 23 '17 at 12:23

score 0 · Answer 1 · answered Nov 23 '17 at 12:24

0

 Sub t()
 Dim x As Range
 Dim y As Range
 Set x = Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight))
 Set y = Range(Range("E1"), Range("E1").End(xlDown).End(xlToRight))
 Range("h1:i" & Range("A1").End(xlDown).Row).FormulaArray = "=" & x.Address & "*" & y.Address
 End Sub

answered Nov 23 '17 at 12:24

Harassed Dad

4,669
1
10
12

[Matrix multiplication](https://en.wikipedia.org/wiki/Matrix_multiplication) is different to fifth grade multiplication. – GSerg Nov 23 '17 at 12:51

Dmitry Pavliv · Answer 2 · 2017-11-23T12:32:04.083

You can do it in this way

Dim m1 As Range
Dim m2 As Range
Dim res As Range

Set m1 = Range("A1").Resize(2, 2)
Set m2 = Range("E1").Resize(2, 2)

Set res = Range("W1").Resize(2, 2)

With res
    .FormulaArray = "=MMULT(" & m1.Address & "," & m2.Address & ")"
    .Value = .Value ' saves result of formulae calculation
End With

If you want to keep a formulae in W1 just remove line .Value = .Value which saves calculation results as plain numbers.

Also be very careful with xlDown and xlRight VBA- Why End(xlDown) will take me to the very bottom of my excel

How to properly define a matrix in VBA?

2 Answers2