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,

Answers


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

Need Your Help

Open xml file on listview item click

android eclipse listview

I'm trying to open a new xml or text file when the user select an item on listview. Below is my code:

github api v3 create issue message not found

ruby github github-api octokit

when i try to create an github issue it gives message not found reply. and also how to send authentication headers with this. because creating issues requires user to be logged in or authenticated