orcas
03-11-2013, 05:47 AM
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?
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?