Function MMult in VBA not working

[Timi]

Hey Everybody

I have a problem with the MMult function in VBA. Although I tried everything (also read the old post concerning MMult in 2006), I can't make it work. Here's my code. Everything works except for the MMult function.

If I paste the values from arrz and CovarMat in a Spreadsheet and use the MMult there it works perfectly. I think the problem must be the CovarMat definition, but I can't figure out why.

Sub Spread_Sim()
    Application.ScreenUpdating = False
 
    Dim Spreads As Double
    Dim n_sim As Double
 
    n_sim = Range("n_sim")
 
    'Polar method for x ~ N(0,1)
    Dim i As Integer
    Dim q, u1, u2, p As Double
    Dim arrz() As Double
    ReDim Preserve arrz(n_sim)
    Dim CovarMat() As Variant
    ReDim Preserve CovarMat(12, 12)
 
    CovarMat = Range("CovarMat")
 
    'For j = 1 To n_sim
        For i = 1 To 12 Step 2
            Do Until q <= 1 And q > 0
                u1 = Rnd() * 2 - 1
                u2 = Rnd() * 2 - 1
                q = u1 ^ 2 + u2 ^ 2
            Loop
            p = Sqr((-2 * Application.WorksheetFunction.Ln(q)) / q)
            arrz(i) = u1 * p
                If i = 13 Then
                Else
                arrz(i + 1) = u2 * p
                End If
                q = 2
        Next i
 
        Spreads = Application.WorksheetFunction.MMult(arrz, CovarMat)
        Excel.Worksheets("Changes in economic environment").Range("A180:L180") = Spreads
 
    'Next j
End Sub

PS: the additional "for" will be used later

Thanks for your help
Timi

 

[Sandeep Warrier]

Hi & welcome to the Board!

MMULT returns an array. You have equated the MMULT result to the variable Spreads, which has been defined as Double.

I'm not 100% sure, but you can try defining it as

Dim Spreads() As Double

and then ReDim Spreads to the required dimension..... or define it as Variant.

 

[Timi]

Hey Sandeep

thx for helping me, unfortunately this doesn't seem to be the problem, I tried both Redim and Variant

Dim Spreads() As Double
ReDim Preserve Spreads(12)

The Error-Message tells me that the MMult-property of the WorksheetFunction-Object can't be assigned (corresponding translation from German Excel)

Regards
Timi

 

[Timi]

Hey just solved it: me-stupid chose the wrong dimension for arrz

Sorry for the inconvenience and thanks for your help

Timi