Arrays using MMULT Function

[Sully1440]

Hi All,
I'm trying to use the mmult function for a Markov Chain. On my sheet, I've used the formula to get the correct answer in one table. However, I'm trying to use VBA to recreate it in another table just below it using the code below. (See attached sheet). I think I'm really close because the formula is correct for the first pass, but I can't seem to get the data to drag across using the code.

Need Help
Thanks,
Jim

Option Explicit
Sub Mmult()
Dim i As Variant

For i = 1 To 15
Cells(18, i + 4) = matrix(1, 1)
Cells(19, i + 4) = matrix(2, 1)
Cells(20, i + 4) = matrix(3, 1)
Next i

End Sub

Function matrix() As Variant ' added As Variant
Dim v As Variant
Dim i As Variant
Dim j As Variant

Dim P(1 To 3, 1 To 3) ' 1 To 3 in both
For j = 1 To 3
For i = 1 To 3
P(i, j) = Cells(i + 4, j + 6)
Next i
Next j

Dim Q(1 To 3, 3 To 3) ' 1 To 1
For i = 1 To 3
Q(i, 3) = Cells(i + 4, 3)
Next i

v = Application.WorksheetFunction.Mmult(P, Q)

matrix = v
End Function

[Paul_Hossler]

I'd use something simple like this

Option Explicit

Sub Mmult()
    Dim i As Variant
    Dim rStart As Range, rTrans As Range, rDest As Range, rMM As Range
    
    Set rTrans = Range("G5:I7")
    Set rStart = Range("D18:D20")
    Set rDest = Range("E18:S20")

    For Each rMM In rDest.Columns
        For i = 1 To rDest.Rows.Count
            rMM.Cells(i, 1).Value = Application.WorksheetFunction.Mmult(rTrans.Rows(i), rStart)
        Next i
        
        Set rStart = rStart.Offset(, 1)
    Next
End Sub

[Kenneth Hobs]

As a Sub, Paul's is a good way to go.

In this method, I show how you can use the code as a sub or as an array formula. It can be tweaked to remove the nRows input.

Sub Test_fMarkov()  Dim a
  a = fMarkov([G5:I7], [D12:D14], 3, 15)
  [E18].Resize(UBound(a), UBound(a, 2)) = a
End Sub


'array formula: =fMarkov(G5:I7, D12:D14, 3, 15)
Function fMarkov(rMatrix, rVector, nRows, nCols)
  Dim a, b, d, m, r As Long, c As Integer
  nCols = nCols + 1
  ReDim a(1 To nRows, 1 To nCols)
  ReDim b(1 To nRows, 1 To 1)
  
  'Set first column
  m = WorksheetFunction.Mmult(rMatrix, rVector)
  For r = 1 To nRows
    a(r, 1) = m(r, 1)
  Next r
  
  'Fill other columns
  For c = 2 To nCols
    For r = 1 To nRows
      b(r, 1) = a(r, c - 1)
    Next r
    m = WorksheetFunction.Mmult(rMatrix, b)
    For r = 1 To nRows
      a(r, c) = m(r, 1)
    Next r
  Next c
  
  'Slice Array a: 1 to nRows, 2 to nCols)
  d = Application.Index(a, Evaluate("row(1:" & nRows & ")"), _
    Application.Transpose(Evaluate("row(1:" & nCols - 1 & ")")))
  
  fMarkov = d
End Function

[Sully1440]

Paul, Kenneth,
Thanks very much. It worked perfectly.
Very simple code

Jim