Multiplying two large matrices in VBA

Question

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,

Solution

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.

Comments

[MGP]

the matrix is just stored in a range?

[skeihani]

sorry, what do you mean by range?

[skeihani]

A has 1000000 rows and 3 columns; B has 3 rows and 3 columns.

[MGP]

Yes, but how did you store the Matrix A and B in Excel?

[Hugues Paquet Blanchette]

What about using Matlab or Scilab ?

[skeihani]

@Marco Getrost, thanks for helping me. It's a big code but part of it which is related to this issue is as follow

[John Coleman]

This should be moved to be part of your question rather than an answer.