In this case, how should I correctly define the range?

[Ms.Sigma]

There are several columns in a worksheet. I want to calculate the correlation matrix of these columns and display the correlation matrix in a user form.

In user form, there are one refedit box, one list box, and one command button.
The refedit box is for data selection; list box is for displaying correlation matrix. When command button is clicked, the correlation matrix will be displayed.

Here is my macros:

Function CorrelationMatrix(rng As Variant) As Variant
' Returns correlation matrix of a range
Dim i As Integer, j As Integer, K As Integer, ncols As Integer, nrows As Integer
Dim r1vector() As Variant
Dim r2vector() As Variant
Dim Cmatrix() As Variant
ncols = rng.Columns.Count
ReDim Cmatrix(ncols, ncols)
nrows = rng.Rows.Count
ReDim r1vector(nrows)
ReDim r2vector(nrows)
For i = 1 To ncols
For K = 1 To nrows
r1vector(K) = rng(K, i)
Next K
Cmatrix(i, i) = 1
For j = i + 1 To ncols
For K = 1 To nrows
r2vector(K) = rng(K, j)
Next K

Cmatrix(i, j) = Application.WorksheetFunction.Correl(r1vector, r2vector)
Cmatrix(j, i) = Cmatrix(i, j)
Next j
Next i

CorrelationMatrix = Cmatrix

End Function

Private Sub cmdCorrelation_Click()
Dim series As Range
Dim correlation As Variant
Set series = Range(RefEdit1.Text)
correlation = CorrelationMatrix(series)

With ListBox1
.Clear
.Font.Size = 9
.List() = correlation
End With
End Sub

The problem is: when I click command button, the correlation matrix cannot be displayed in list box.I think I have mistake for definition of range, but I don't know how should I modify this mistake. Could any one give me some suggestions?

Thanks for all your help!!!

 

[Ms.Sigma]

many many thanks!! I got hint from your macros!!

One way:

Private Sub cmdCorrelation_Click()
    Dim avdCorr      As Variant
    Dim asCorr()    As String
    Dim i           As Long
    Dim j           As Long
 
    avdCorr = CorrelationMatrix(Range("A2:D9"))
    ReDim asCorr(1 To UBound(avdCorr, 1), 1 To UBound(avdCorr, 2))
    
    For i = 1 To UBound(avdCorr, 1)
        For j = 1 To UBound(avdCorr, 2)
            asCorr(i, j) = Format(avdCorr(i, j), "+0.000;-0.000;"" ""0")
        Next j
    Next i
 
    With Sheet1.ListBox1
        .ColumnCount = UBound(asCorr)
        .List() = asCorr
    End With
End Sub