PDA

View Full Version : [SOLVED] Arrays using MMULT Function



Sully1440
05-16-2018, 06:04 AM
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
05-16-2018, 07:14 AM
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
05-16-2018, 09:20 AM
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
05-16-2018, 09:52 AM
Paul, Kenneth,
Thanks very much. It worked perfectly.
Very simple code

Jim :)