View Full Version : Sleeper: Matrix multiplication in VBA (not the application. one)

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)

06-10-2015, 05:15 AM
The operators +-*/ do not work with arrays.

06-10-2015, 06:46 AM
Break it down

Sub a()
Dim Nomin As Double
Dim Denom As Double
Dim WsF As Object
Set WsF = Application.WorksheetFunction

With WsF
Nomin = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu))
Denom = (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))
End With

TMVMuTanN = Nomin / Denom
End Sub

06-10-2015, 07:19 AM
That still won't work since MMult always returns an array. If the array contains only one value, then you could use:

With WsF
Nomin = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu))(1)
Denom = (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))(1)
End With

06-10-2015, 08:50 AM
I just have one question
What are financial functions ? :D

06-13-2015, 05:48 AM
1. I don't think you divide Nomin by Denom. I think you multiple Nomin by the inverse of Denom

2. As to the 'scaler', do you mean the matrix determinate? if so you can use the MDETERM() function after #1

Forgetting the VBA and Excel functions for a minute -- what is the formula in words / math?

And as check, do you have inputs that provide a known answer?

06-13-2015, 02:50 PM

"A Simple Spreadsheet-Based Exposition of the Markowitz Critical Line Method for Portfolio Selection"

Over my head :think: but at least it has 'spreadsheet' in the title