Matrix Multiplication of Arrays

[orcas]

Hello :hi:

I am having problems doing matrix multiplication in VBA. As you will see from my code, the actual matrix multiplication works well, but whenever it reached a point where i needed to multiply the new vectors by a scalar, i get an error message of "Type Mismatch"


I have included the spreadsheet if you wish to take a look


Sub MatrixMultiply()
Dim CovMat() As Double, PortWeights(), x As Integer, y As Integer, z As Integer, Omega(), i As Integer, WF As WorksheetFunction
Dim tempholder(), tempholder2(), j As Integer, NewOmega()
Set WF = Application.WorksheetFunction
L1: ' Get the weights in the first box
x = [f1].CurrentRegion.Columns.Count
z = [f1].CurrentRegion.Rows.Count
ReDim PortWeights(z, x)
For y = 1 To z
For j = 1 To x
PortWeights(y, j) = Cells(y, j + 5)
Next j
Next y
L2: 'Get the matrix in the second Box
x = [f11].CurrentRegion.Columns.Count
z = [f11].CurrentRegion.Rows.Count
ReDim CovMat(1 To z, 1 To x)
For y = 1 To z
For j = 1 To x
CovMat(y, j) = Cells(y + 10, j + 5)
Next j
Next y
L3: 'Run the Matrix Multiplication loop
ReDim Omega(1 To UBound(PortWeights))
ReDim NewOmega(1 To UBound(PortWeights))
For i = 1 To UBound(PortWeights)
ReDim tempholder(1 To UBound(PortWeights, 2))
ReDim tempholder2(1 To UBound(PortWeights, 2))
For x = 1 To UBound(PortWeights, 2)
tempholder(x) = PortWeights(i, x)
Next x
tempholder2 = WF.Transpose(tempholder)
Omega(i) = WF.MMult(WF.MMult(tempholder, CovMat), (tempholder2))
ErrorPoint: 'The next line is where I am having issues
NewOmega(i) = Omega(i) * 1.64485
Erase tempholder
Erase tempholder2
Next i
End Sub





Anyone has an idea why this is the case?

[Aflatoon]

You can't multiply an array by a value like that you have to loop through the array and multiply each item.

[orcas]

I appreciate your response Aflatoon.

if you take a look at the point where i seem to be having this error


NewOmega(i) = Omega(i) * 1.64485

The statement is actually referring to each element in the Omega array and not the entire array at once, is there something I am missing here?

[Aflatoon]

Each element of omega is itself a variant array because MMult returns an array. You need to use

Omega(i)(1) * 1.64485
since you only have one item in each array.

[orcas]

Got it! Thanks a lot man. I really appreciate your help, if you ever come by London, I buying your drinks!

[Aflatoon]

Risky thing to say - I happen to be in London right now... ;)

[snb]

Instead of using:


x = [f1].CurrentRegion.Columns.Count
z = [f1].CurrentRegion.Rows.Count
ReDim PortWeights(z, x)
For y = 1 To z
For j = 1 To x
PortWeights(y, j) = Cells(y, j + 5)
Next j
Next y

you can use:

PortWeights = [f1].CurrentRegion.Value