VBA Correlation matrix with Paramarray

[kisslaszlo123]

Hello,

I have an excel sheet with multiple columns and I need to create correlation matrix for some columns that are not necessarily next to each other. I tried to replace the range with paramarray in an UDF, but didn't work and I have no idea about how to solve this problem.

Function CorrelationMatrix(ParamArray rng() As Variant) 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

Thanks for your help in advance!
L.

 

[Eric W]

Welcome to the forum!

You're trying to use methods that don't belong to the objects you're using. Try this:

Function CorrelationMatrix(ParamArray rng() As Variant) As Variant
Dim i As Long
Dim j As Long
Dim matrix() As Double

    ReDim matrix(0 To UBound(rng), 0 To UBound(rng))

    For i = LBound(rng) To UBound(rng)
        For j = i To UBound(rng)
            matrix(i, j) = WorksheetFunction.Correl(rng(i), rng(j))
        Next j
    Next i
    
    CorrelationMatrix = matrix
End Function

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2	1		1			2	0.5		1	0.83205	0.972272	0.892844
3	2		3			3	4		0	1	0.686406	0.923851
4	3		5			4	4		0	0	1	0.79524
5	4		3			6	4		0	0	0	1
6	5		7			6	7
7
Sheet13

Cell Formulas
Range	Formula
J2:M5	J2	=correlationmatrix(B2:B6,D2:D6,G2:G6,H2:H6)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[kisslaszlo123]

Thank you very much for your help, it works!

 

[Eric W]

Glad to help!