# Matrix multiplication in VBA (not the application. one)

• 06-09-2015, 10:10 AM
amri
Matrix multiplication in VBA (not the application. one)
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!

-----------------------------------------------------------------------------
Code:

```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
Aflatoon
The operators +-*/ do not work with arrays.
• 06-10-2015, 06:46 AM
SamT
Break it down
Code:

```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
Aflatoon
That still won't work since MMult always returns an array. If the array contains only one value, then you could use:
Code:

```    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
SamT
I just have one question
What are financial functions ? :D
:bug:
• 06-13-2015, 05:48 AM
Paul_Hossler
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
Paul_Hossler
http://epublications.bond.edu.au/cgi...&context=ejsie

"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