amri

06-09-2015, 10:10 AM

Hello everyone

I'm currently building a set of VBA functions to calculate some financial stuff. I have to do a lot of matrix multiplication, and i've ran into a very annoying issue. I'm sure it's just me who doesn't understand how VBA works properly yet.

So, in this example, i'm calculating a scalar from a set of matrices. My issue is, however, that i simply does not work. The nominator and denominator calculates perfectly seperatly and i can also output them - but i cannot take the fraction between them. Also, just to test if it was a general problem i noticed that i cannot subtract, multiply them either. So i suppose that i'm doing something generally wrong. Here's my code

Can anyone explain to me what i'm doing wrong? Help is greatly appreciated as this really annoys me!

-----------------------------------------------------------------------------

Public Function TMVMuTanN(ExcessMu As Variant, Sigma As Variant) As Variant

Dim onevector As Variant

ReDim onevector(1 To 3, 1 To 1)

Dim i As Double

onevector(1, 1) = 1

onevector(2, 1) = 1

onevector(3, 1) = 1

With Application.WorksheetFunction

TMVMuTanN = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu)) / (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))

End With

End Function

-----------------------------------------------------------------------------

EDIT: The function takes to matrices as input, excessMu and Sigma (yes it's Markowitz analysis hehe)

I'm currently building a set of VBA functions to calculate some financial stuff. I have to do a lot of matrix multiplication, and i've ran into a very annoying issue. I'm sure it's just me who doesn't understand how VBA works properly yet.

So, in this example, i'm calculating a scalar from a set of matrices. My issue is, however, that i simply does not work. The nominator and denominator calculates perfectly seperatly and i can also output them - but i cannot take the fraction between them. Also, just to test if it was a general problem i noticed that i cannot subtract, multiply them either. So i suppose that i'm doing something generally wrong. Here's my code

Can anyone explain to me what i'm doing wrong? Help is greatly appreciated as this really annoys me!

-----------------------------------------------------------------------------

Public Function TMVMuTanN(ExcessMu As Variant, Sigma As Variant) As Variant

Dim onevector As Variant

ReDim onevector(1 To 3, 1 To 1)

Dim i As Double

onevector(1, 1) = 1

onevector(2, 1) = 1

onevector(3, 1) = 1

With Application.WorksheetFunction

TMVMuTanN = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu)) / (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))

End With

End Function

-----------------------------------------------------------------------------

EDIT: The function takes to matrices as input, excessMu and Sigma (yes it's Markowitz analysis hehe)