Two functions that work separately but not in a nested way

[Prack]

Hi, I need a little help. I developed two functions that work perfectly separately, but gives an error message when using them in a nested way. My intention is to use {=varcovar(Step1(datarange))} [ctrl+shift+enter].

Funtion Step1 takes a range as an input, extracts the mean from each column, divides by the standard deviation of each column, and returns a range of the same size as the original containnig the transformed values. The relevant bits are:

Function Step1(rango As Range)
Dim i, j As Integer
Dim numRows As Integer
Dim numCols As Integer
numRows = rango.Rows.Count
numCols = rango.Columns.Count
ReDim Result(1 To numRows, 1 To numCols) As Double
ReDim x(1 To numRows)
Dim B As Double
Dim C As Double
B = 0
C = 0
For i = 1 To numCols
B = Application.WorksheetFunction.Average(rango.Columns(i))
C = Application.WorksheetFunction.StDev(rango.Columns(i))
For j = 1 To numRows
x(j) = (rango(j, i) - B) / C
Result(j, i) = x(j)
Next j
Next i
Step1 = Result
End Function

The second function is varcovar and takes a range as an input and calculates a (square) covariance matrix. The relevant bit is

Function VarCovar(rng As Range) As Variant
'All operations go here
VarCovar = matrix
End Function

The input in varcovar should be the transformed range obtained from Step1 function.

I hope some one can help me. The best for you all.

 

[AlphaFrog]

VarCovar is expecting a cell range as its input argument. Step1 doesn't deliver a cell range as its result.

Probably the easiest thing to do is to write a 3rd function that is the combination of the other two without having to make the other two work as a nested Array formula.

 

[Prack]

Thanks. Unfortunatelly the VarCovar function is hard to adapt (I find it hard to write the third function you suggest). The most efficient for me would be to use the functions in a nested way.

 

[mikerickson]

Step1 returns an array, not a range.
VarCovar asks for a range (not an array) as an argument.

One way would be to alter VarCover so that it accepts both a range or an array as its argument.

Function VarCovar (inArray as Variant)
    Dim temp As Variant
    If TypeName(inArray) = "Range" Then
        If inArray.Cells.Count = 1 Then
            ReDim temp(1 to 1, 1 to 1)
            temp(1,1) = inArray.Value
        Else
            temp = inArray.Value
        End If
        inArray = temp
    End If
    If Not TypeName(inArray) Like "*()" Then
        ReDim temp(1 to 1, 1 to 1)
        temp(1,1) = inArray
        inArray = temp
    End If

at this point, inArray is an array. Depending on how the rest of VarCovar is written, some things may need to be altered. (e.g. instead of looping through rng.Rows and rng.Columns one would have to loop through UBound(inArray,1) and UBound(inArray,2) ) Fortunatly, most of those changes would be just conversion from range syntax to array syntax. Major logic alterations probably won't be needed.

 

[Prack]

Thank you so much for your tip. Is it possible to get my Step1 function to deliver a range instead of an array? Best regards

 

[mikerickson]

"Is it possible to get my Step1 function to deliver a range instead of an array?"

Sorry, but no.
Step1 looks at a range, and manipulates the values returning an array.
For that information to be passed as a range, the values would have to be written to cells.

Writting to cells or otherwise modifying them cannot done by a UDF called by a worksheet.

 

[Prack]

thanks a lot, I really apreciate your help

 

[Prack]

Looks like I'm gonna need a little more help. The following is my function to calculate a covariance matrix and it works ok. I would like to change it so that before performing the Covar operation, the columns of the original range (rng.Columns(i) and rng.Columns(j)) are replaced by those same columns minus their average value which I would obtain using Application.WorksheetFunction.Average(rng.Columns(i)). Hope someone can guide me.

Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.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(rng.Columns(i), rng.Columns(j))
Next j
Next i
VarCovar = matrix

End Function

 

[mikerickson]

On the rush at work. Try

matrix(i-1, j-1) = Application.Covar(Application.Index(someArray,0,i), Application.Index(someArray,0,j))

Note the use of Application.Index to return a single column from an array. This is the array equivilant of using .Column(i) to return a single column from a range.

 

[Prack]

Thank you very much, its the most elegant and usefull piece of code I could have wished for. Let me know if I can be of any help for you.