Passing array from one function into another function

[ishus]

As this is my first post and related to an assignment from a class I am taking, I hope it is within all guidelines of the forum.

The requirements for the assignment were originally to create an efficient frontier from two stock assets using some VBA and Excel Formulas. To extend the assignment and my own learning I was looking to make the sheet more dynamic and reliant on VBA especially because I encounter similar problems at work with manipulating arrays.

The new sheet can accept data up to 10 assets with variable length of historical data. A named range "Assetsrng" was used to make the columns dynamic. I wrote the function "ReturnsCalcMatrix" below to receive the dynamic named range, check whether blank columns exist, and return a matrix of calculated returns for only the columns holding values.

The function "VarCovar" calculates a Covariance Matrix for which I will use Cntrl+Shft+Enter in the sheet to call and return the matrix. The problem is I am not able to use the output of function "ReturnsCalcMatrix" in "VarCovar".

I have tried many things but usually I receive an error stating that when I use the output of "ReturnsCalcMatrix" in the "VarCovar" function I always get an Invalid Qualifier error as the properties .Columns.Count,.Rows.Count,Columns(i) in "VarCovar" are not usable on the output from the other function.

I have tested both functions independently and seem to work when passing ranges from a sheet. Basically, now I want the output of my first function to be an array or format that I can use in the second function.

Have also attached the spreadsheet with some test data, although I have deleted all functions that are not related to this question.

[VBA]Option Explicit
Function ReturnsCalcMatrix(Assetrng1 As Range) As Variant
Dim LastRow As Integer
Dim i, j As Integer
Dim Count As Integer
Dim ReturnMatrix()

LastRow = Assetrng1.Cells(Assetrng1.Count).Row - 1
Count = 0

For j = 2 To 11
If Worksheets("Sheet1").Cells(2, j).Value <> "" Then
Count = Count + 1
ReDim Preserve ReturnMatrix(LastRow - 3, Count - 1)
For i = 2 To LastRow - 1
ReturnMatrix(i - 2, j - 2) = Log((Worksheets("Sheet1").Cells((i + 1), j).Value) / (Worksheets("Sheet1").Cells(i, j).Value))
Next i
Else
End If
Next j

ReturnsCalcMatrix = ReturnMatrix
End Function

Function VarCovar(Assetrng2 As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
Dim numrows As Integer
numcols = Assetrng2.Columns.Count
numrows = Assetrng2.Rows.Count

Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)

For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(Assetrng2.Columns(i), Assetrng2.Columns(j)) * (numrows / (numrows - 1))
Next j
Next i
VarCovar = matrix
End Function[/VBA]

Thanks for your time!

[Bob Phillips]

I assume ReturnsCalcMatrix would accept a range such as B2:B4, but what would VarCovar accept?

[ishus]

It will accept a range as well and produce a covariance matrix between the columns of the range.
But I would like it to accept "ReturnMatrix" from ReturnsCalcMatrix or be able to use the output matrix from ReturnsCalcMatrix to feed VarCovar.

[Bob Phillips]

You said that already, but what should VarCoVar accept? You only have one parameter, CoVar accepts two so I cannot see what you would pass if you were just passing worksheet ranges (which you say you tested).

[ishus]

ah..I think I need to make VarCoVar handle arrays and not just ranges

Thanks

[Teeroy]

I don't follow why you're multiplying the covar by a ratio (numrows / (numrows-1)) but aside from that couldn't you just enter your UDF as an array formula on the worksheet? If you want to pass an array directly as well as a range then you're right, you would need the parameter as a variant.

[ishus]

It's because for the Covar function I don't believe Excel distinguishes between population and sample. It has Var and Varp for variance, and Stdevp and Stdev for standard deviation but only Covar for covariance as far as I know.