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.
Thank a lot in advance,
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.
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