User Defined Functions...Trouble With Output Values

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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?

Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Code:
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.
 
Last edited:

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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?

Code:
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

Well-known Member
Joined
Jul 26, 2014
Messages
614
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.

Code:
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
 

Forum statistics

Threads
1,148,294
Messages
5,745,944
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top