# VBA Function for Correlation Matrix

#### mikes_r

##### New Member
Hi,

I'm new to using VBA, but looking to learn. I'm having trouble with the code for a correlation matrix function. The code is below and I keep getting a #NAME? error but I can't figure out where the issue is.

Function CorrelationMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim NumColumns As Integer

NumColumns = rng.Columns.Count - 1

Dim matrix() As Double
ReDim matrix(NumColumns, NumColumns)

For i = 0 To NumColumns
For j = i To NumColumns
matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i
CorrelationMatrix = matrix
End Function

Any help/advice would be greatly appreciated!

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### shg

##### MrExcel MVP
Your function works OK for me:

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ 1​ 2​ 9​ 2​ 5​ 1​ -0.34013​ -0.32348​ 0.159371​ F1:I4: {=CorrelationMatrix(A1:D20)} 2​ 2​ 4​ 5​ 5​ 0​ 1​ 0.012126​ 0.065559​ 3​ 3​ 7​ 9​ 3​ 0​ 0​ 1​ 0.168794​ 4​ 1​ 2​ 8​ 3​ 0​ 0​ 0​ 1​ 5​ 3​ 4​ 8​ 9​ 6​ 7​ 4​ 9​ 3​ 7​ 8​ 1​ 3​ 7​ 8​ 4​ 4​ 8​ 7​ 9​ 8​ 3​ 3​ 6​ 10​ 6​ 7​ 4​ 9​ 11​ 8​ 6​ 2​ 3​ 12​ 2​ 3​ 2​ 1​ 13​ 4​ 4​ 8​ 7​ 14​ 2​ 7​ 6​ 3​ 15​ 1​ 2​ 6​ 6​ 16​ 5​ 3​ 4​ 2​ 17​ 9​ 2​ 3​ 7​ 18​ 1​ 9​ 4​ 5​ 19​ 5​ 5​ 6​ 9​ 20​ 2​ 8​ 8​ 9​

What version of Excel are you using?

#### mikes_r

##### New Member
Hi shg,

I'm using Excel 2016. I didn't even think of testing with a random number generator before, I have now on the relevant document and its still giving the #NAME? error.

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

Where is the code? Is it in a standard module?

Last edited:

#### mikes_r

##### New Member

Hi pgc01,

I've deleted and then readded the module. It is now part of the workbook as a standard module and it is now sort of working, but not for what I need it for. It works for creating a correlation matrix of closing prices of stocks, but I'm trying to use it for a correlation matrix of the percentage change of closing prices. Using it on percentage change data is giving returning a #VALUE! error. (the percentage data is calculated from the closing price data, so there shouldn't be anything funny about it)

#### shg

##### MrExcel MVP
Can you provide an example that returns an error?

Can you write a formula for the result you're trying to achieve?

Last edited:

#### mikes_r

##### New Member
I figured it out! It was an issue with the data set, one stock had incomplete/NA values and that was screwing with the entire array.

Thanks for everyone's help!

You're welcome.

Replies
3
Views
216
Replies
2
Views
247
Replies
3
Views
917
Replies
1
Views
354
Replies
0
Views
32