Dividing matrices using a VBA function

Question

Very new to VBA so apologize if it's a really simple question. This is finance/ portfolio management related.

I would like to create a VBA function that can give me the Global Minimum Variance Portfolio (GMVP) weights of a set of assets with just the assets' variance-covariance matrix.

I'm able to key in this formula directly into the cells on Excel to get the weights: =MMULT(MINVERSE(S),Ones)/SUM(MMULT(MINVERSE(S),Ones)) where S is the variance-covariance matrix and ones is a column matrix containing "1"s

There's no issues with mapping the dimensions of the matrices (if S is 5x5 then ones will be 5x1)

I've been trying to make a VBA function so that I don't have to type the lengthy formula every time to find the GMVP. Here's the code I have. Before this I also tried just writing everything in a really long single line, using "/" to divide (didn't work)

I wonder if it is because we can't use "/" to divide matrices in VBA? Is there a way to make the formula into a function?

Thank you!

Function GMVPcol(S As Range, Ones As Range) As Range
Dim num As Range
Dim dem As Range

num = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(S), Ones)
dem = Application.WorksheetFunction.Sum(Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(S), Ones))

GMVPcol = Application.WorksheetFunction.MMult(num, Application.WorksheetFunction.MInverse(dem))

End Function

Range is an object in VBA. Thus, it requires the key-word set. And it should be set to an object as well. — Vityata

Vityata Vityata · Accepted Answer · 2019-02-28T13:08:49

If you simplify your formula extremely, to something like this:

then, this is the correct way to represent it as a VBA function and call it in Excel:

Public Function SimpleMultiplication(matrix1 As Range, matrix2 As Range) As Variant
    SimpleMultiplication = WorksheetFunction.MMult(matrix1, matrix2)
End Function

The function returns a variant, not a range. And in your code, make sure to always use the keyword Set, when an object of type range is to be assigned. In general, if you want to pass through a middle range and assign it, then it is better to work with an array and take the numbers from there - Creating an Array from a Range in VBA

Dividing matrices using a VBA function

1 Answers