# User Defined Functions...Trouble With Output Values

#### SBF12345

##### Well-known Member
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
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
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
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``````

Replies
2
Views
178
Replies
2
Views
484
Replies
0
Views
117
Replies
21
Views
251
Replies
7
Views
81

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?

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