# Multiplying two large matrices in VBA

I need your help to multiply two matrices A and B in VBA. where A(1000000*3) and B(3*3) mmult function doesn't work for multiplying large matrices. Any idea how this can be done.

My guess is that mmult is using 16-bit integers for its indices, which isn't enough to accommodate 1,000,000 rows. You can write your own function to take two variants containing arrays and returns a variant containing the product. It is enough to use Long rather than Integer for the index variables:

```Function MatrixProduct(A As Variant, B As Variant) As Variant
'Assumes that A,B are 1-based variant arrays

Dim m As Long, n As Long, p As Long, i As Long, j As Long, k As Long
Dim C As Variant

If TypeName(A) = "Range" Then A = A.Value
If TypeName(B) = "Range" Then B = B.Value

m = UBound(A, 1)
p = UBound(A, 2)
If UBound(B, 1) <> p Then
MatrixProduct = "Not Defined!"
Exit Function
End If
n = UBound(B, 2)

ReDim C(1 To m, 1 To n)
For i = 1 To m
For j = 1 To n
For k = 1 To p
C(i, j) = C(i, j) + A(i, k) * B(k, j)
Next k
Next j
Next i
MatrixProduct = C
End Function
```

To test this I wrote a function to create random matrices:

```Function RandMatrix(m As Long, n As Long) As Variant
Dim A As Variant
Dim i As Long, j As Long
ReDim A(1 To m, 1 To n)

Randomize
For i = 1 To m
For j = 1 To n
A(i, j) = Rnd()
Next j
Next i
RandMatrix = A
End Function
```

And then I ran this:

```Sub test()
Dim start As Double
Dim cases As Long
Dim A As Variant, B As Variant, C As Variant

cases = 1000000
A = RandMatrix(cases, 3)
B = RandMatrix(3, 3)
start = Timer
C = MatrixProduct(A, B)
MsgBox (Timer - start) & " seconds to compute the product"

End Sub
```

On my machine it takes about 1.7 seconds.

@Marco Getrost

```cases=1000000
c=3
T is a 3*3 matrix
r = Application.MMult(randn(cases, c), T)

Function randn(rows As Variant, cols As Variant) As Variant
Dim mymat() As Variant
ReDim mymat(1 To rows, 1 To cols)
Dim i, j As Variant
For i = 1 To rows
For j = 1 To cols
mymat(i, j) = Application.NormInv(Rnd(), 0, 1)
Next j
Next i
randn = mymat
End Function
```