User Defined Functions...Trouble With Output Values

[SBF12345]

Below is a user defined function i put together to gain flexibly outputting correlation matrix values to a location of my choosing. The problem is, when I enter my data range and click OK I only get one value. The value is a "1" which is a good sign (lies on the diagonal), but when I try to expand the selection I get error values. How can I expand the function output value to cover the entire matrix space?

I did include an 'output' section near the bottom of my code which mimics another UDF output format. How can I see all the correlation values in my matrix?

Public Function CorrelMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer


Dim num_Cols As Integer: num_Cols = rng.Columns.Count
Dim num_Rows As Integer: num_Rows = rng.Rows.Count


Dim CMat() As Double
ReDim CMat(num_Cols, num_Cols)


    For i = 1 To num_Cols
        For j = 1 To num_Cols
            CMat(i, j) = Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
        Next j
    Next i
CorrelMatrix = CMat


'Output


For i = 1 To num_Cols
    For j = 1 To num_Cols
        'CMat(i, j) =
    Next j
Next i
        
End Function

 

[rlv01]

You define variable num_Rows and set it to the number of rows in your input range. But then you never use num_Rows for anything. CMat is defined only in terms of num_Cols

ReDim CMat(num_Cols, num_Cols)

Which seems odd. Is that intentional? I'd have expected num_Rows to appear in the CMat definition, and in either the i or j looping.

 

[SBF12345]

I removed the num_Rows variable. The correlation matrix uses correlation values for sets of data between all combinations of column variables and so a (num_cols, num_cols) array is produced. Below is the updated script.

Before I click 'OK' in the user defined function box the output array appears to give numbers that are reasonable. After clicking 'OK' I get a '1' value in the upper left corner which is an accurate value, but I cannot seem to expand the output values to fill the entire matrix (of num_cols by num_cols cells). How am I able to expand the output to fill all the matrix values?

Public Function CorrelMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer


Dim num_Cols As Integer: num_Cols = rng.Columns.Count


Dim CMat As Variant
ReDim CMat(num_Cols, num_Cols) As Variant
Dim output() As Variant


    For i = 1 To num_Cols
        For j = 1 To num_Cols
            CMat(i, j) = Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
        Next j
    Next i
    
'ReDim output(i, j) As Variant


'For i = 1 To num_Cols
    'For j = 1 To num_Cols
        'output(i, j) = CMat
    'Next j
'Next i


CorrelMatrix = CMat


End Function

 

[SBF12345]

OK, got it working...The code below returns an array 'CMat' and should be scalable for different size matrices. Of course ctrl+shift+enter is required to return an array.

Public Function CorrelMatrix(rng As Range) As Variant

'highlight entire space of matrix output and use ctrl + shift + enter to return the array


Dim i As Integer
Dim j As Integer


Dim num_Cols As Integer: num_Cols = rng.Columns.Count


Dim CMat As Variant
ReDim CMat(num_Cols, num_Cols) As Variant
Dim output() As Variant


    For i = 1 To num_Cols
        For j = 1 To num_Cols
            CMat(i, j) = Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
        Next j
    Next i


CorrelMatrix = CMat


End Function