matrix multiplication vba

[gmgonzal]

Hello,

I need your help with my code. When it arrives to the line of variance it returns an error and according to me the dimensions of the matrix are correct:

Sub calStatistic()

Dim calVariance, calBeta, calDownsideBeta As Boolean
Dim Beta, Downsidebeta, Variance As Single
'Dim i As Integer, j As Integer
'Dim ticker() As String
Dim Betas As Range
Dim Weights As Range
Dim Downsidebetas As Range
Dim WF As WorksheetFunction
Dim Cov As Range
Dim temp As Variant

Set WF = WorksheetFunction

ThisWorkbook.Worksheets("Current Holdings").Activate



Set Betas = Range("D2", Range("D2").End(xlDown))

Set Weights = Range("I2", Range("I2").End(xlDown).Offset(-2, 0))

Application.Workbooks.Open ("C:\20 Stocks Return proof.xlsm")
Worksheets("Covariances").Activate
Set Cov = Range("B2:u21")

Worksheets("Downsidebeta").Activate
Set Downsidebetas = Range("B2", Range("B2").End(xlDown)) ' this is a 20x1 vector

ThisWorkbook.Activate

Beta = WF.MMult(WF.Transpose(Betas.Value), Weights.Value)

Downsidebeta = WF.MMult(WF.Transpose(Downsidebetas.Value), Weights.Value)

temp = WF.MMult(WF.Transpose(Weights.Value), Cov.Value)

Variance = WF.MMult(temp, Weights.Value)

Worksheets("Current Holdings").Activate

Range("a39") = Cov
Range("g25").Value = Beta
Range("g26").Value = Downsidebeta

End Sub


Just in case I am a beginner.

Thank you very much!

 

[Jerry Sullivan]

Hello and Welcome!

Try making these two changes:

Dim Beta As Variant, Downsidebeta As Variant, Variance As Variant

Variance = Application.MMult(temp, Weights.Value)

Note that when you declare Variables in one statement like this....

Dim Beta, Downsidebeta, Variance As Single

the "as Single" type only applies to the last variable. The other two variables are assigned as the default Variant data type.

If your intent is to declare these all as Singles, you need to declare that for each variable...

Dim Beta as Single, Downsidebeta as Single, Variance As Single

Hope this helps.