CODEWITHSUNDEEP
vbaexcelmatrix-multiplication
Doug Coats
Doug Coats

Reputation: 7107

Matrix Multiplication Throwing SubScript Out of Range Error

Preface: Messing around with cube math and after looking around at all the various options I seem to have run into an issue with my current structure. Maybe someone would love to point out what I am missing here?

Private Sub cube3()
    Dim x(0 To 2, 0 To 2) As Single
    Dim y(0 To 2, 0 To 2) As Single
    Dim z(0 To 2, 0 To 2) As Single
    For a = 0 To 2
        For b = 0 To 2
            Count = (Count + 3) / 2 * 1.5
            x(a, b) = Count
            y(a, b) = Count
            z(a, b) = Application.WorksheetFunction.MMult(x, y)(a, b) '****** This is where the issue occurs
            Debug.Print ; x(a, b)
            Debug.Print ; z(a, b)
        Next
    Next
End Sub

Upvotes: 1

Views: 248

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

In this instance MMULT() is not the way to go it is not made for single value multiplication.

It systematically does something similar to SUMPRODUCT(). As it multiplies each item in a row in the First array with each item in a column in the second array.

enter image description here

Becomes

enter image description here

See HERE for a much better explanation.

In this code you are multiplying single values so a simple multiplication would work:

= x(a,b)*y(a, b)

As far as my research has found to multiply each value in one array with its sister value in a second array, the only method is to loop.

You are already using the quickest method by which to do this. By using array instead of worksheet ranges even with larger datasets this will still be pretty quick.

If you want to use the MMULT() then fill both arrays first and declare z as a variant:

Private Sub cube3()
Dim x(0 To 2, 0 To 2) As Single
Dim y(0 To 2, 0 To 2) As Single
Dim z
For a = 0 To 2
    For b = 0 To 2
        Count = (Count + 3) / 2 * 1.5
        x(a, b) = Count
        y(a, b) = Count

        Debug.Print x(a, b)
        Debug.Print y(a, b)
    Next
Next
 z = Application.WorksheetFunction.MMult(x(), y()) '****** This is where the issue occurs
 Range("A1").Resize(UBound(z, 1), UBound(z, 2)).Value = z
End Sub

Upvotes: 1

Related Questions