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!!!

 

[Ruddles]

Immediately after Set series = Range(RefEdit1.Text), insert MsgBox series.Address - that will tell you whether you've set the range correctly.

In Function CorrelationMatrix(rng As Variant) As Variant, MsgBox rng.Address will confirm it arrived safely.

Are you absolutely sure that any of your code is actually running when you click the command button? Did you try the suggestions I made at http://www.mrexcel.com/forum/showpost.php?p=2631518&postcount=5?

 

[shg]

Unless you use Option Base 1, arrays in VBA are zero-based.

Function CorrelationMatrix(rng As [COLOR=red]Range[/COLOR]) As Double()
    Dim nCol        As Long
    Dim iRow        As Long
    Dim jRow        As Long
    Dim adCorr()    As [COLOR=red]Double[/COLOR]
 
    nCol = rng.Columns.Count
    ReDim adCorr([COLOR=red]1 To nCol, 1 To nCol[/COLOR])
 
    For iRow = 1 To nCol
        adCorr(iRow, iRow) = 1
 
        For jRow = iRow + 1 To nCol
            adCorr(iRow, jRow) = WorksheetFunction.Correl([COLOR=red]rng.Rows(iRow), rng.Rows(jRow)[/COLOR])
            adCorr(jRow, iRow) = adCorr(iRow, jRow)
        Next jRow
    Next iRow
 
    CorrelationMatrix = adCorr
End Function

For example,

      -A- -B- -C- -D- E --F--- --G--- --H--- --I---
  1   182 315 459 427   1.000  0.960  0.896  0.835 
  2   106 367 600 717   0.960  1.000  0.980  0.943 
  3   145 288 498 677   0.896  0.980  1.000  0.990 
  4   180 247 457 659   0.835  0.943  0.990  1.000 
  5   179 367 521 608                              
  6   178 377 468 669

The array formula in F1:I4 is =CorrelationMatrix(A1:D6)

 

[shg]

Sorry, that was all wet. Trying again:

Function CorrelationMatrix(rng As Range) As Double()
    Dim nRow        As Long
    Dim iRow        As Long
    Dim jRow        As Long
    Dim adCorr()    As Double
 
    nRow = rng.Rows.Count
    ReDim adCorr(1 To nRow, 1 To nRow)
 
    For iRow = 1 To nRow
        adCorr(iRow, iRow) = 1
 
       For jRow = iRow + 1 To nRow
            adCorr(iRow, jRow) = WorksheetFunction.Correl(rng.Rows(iRow), rng.Rows(jRow))
            adCorr(jRow, iRow) = adCorr(iRow, jRow)
        Next jRow
    Next iRow
 
    CorrelationMatrix = adCorr
End Function

Example:

      -A- -B- -C- -D- E --F--- --G--- --H--- --I--- --J--- --K---
  1   178 245 600 619   1.000  0.748  0.784  0.860  0.862  0.999 
  2   176 250 308 709   0.748  1.000  0.505  0.964  0.920  0.777 
  3   107 382 473 419   0.784  0.505  1.000  0.715  0.802  0.785 
  4   129 328 449 788   0.860  0.964  0.715  1.000  0.990  0.882 
  5   127 260 308 444   0.862  0.920  0.802  0.990  1.000  0.883 
  6   173 224 453 483   0.999  0.777  0.785  0.882  0.883  1.000

 

[Ms.Sigma]

Hello Ruddles and shg4421! Thanks a lot for your help!

Now, my user form is running after I defined "option base 1" at the beginning.

However, I met another problem. The list box can only display the correlation coefficients of each pair of columns rather than the correlation matrix. For example: due to there are 6 columns, thus, the list box only displays 6 correlation coefficients. How should I define the properties of list box so that it can display the correlation matrix?

Thanks again!!!

 

[shg]

Increase the ColumnCount property of the list box as needed.

 

[Ms.Sigma]

The correlation coefficients of my list box have many decimal places. How should I format text of list box so that I can have 4 decimal places.

For example, I want the correlation coefficient to be 0.1234, instead of 0.123456789123456789.

Furthermore, I want 0.1234, instead of .1234.

What should I do?

 

[Ms.Sigma]

 

[mirabeau]

maybe (or maybe) not of use to you, but here's a couple of codes.

The first one generates some test data (use blank sheet) and second produces correlations. Use as you like.

Sub testdata() 'for correlation
Sheets("sheet2").Activate
ActiveSheet.UsedRange.ClearContents
With Range("A2:D12")
    .Formula = "=int(rand()*15)"
    .Value = .Value
End With
For j = 1 To 4
    Cells(1, j) = "Series" & j
Next j
End Sub

Sub correlat()
Dim n, m, a(), i, j
Sheets("sheet2").Activate
With Range("A1").CurrentRegion
    nr = .Rows.Count
    nc = .Columns.Count
    .Resize(nr - 1, 1).Offset(1).Name = "xx"
ReDim a(1 To nc, 1 To nc)
For i = 1 To nc: For j = i To nc
    a(i, j) = "=Correl(Offset(xx,0," & i _
        & "- 1), Offset(xx,0," & j & "-1))"
a(j, i) = a(i, j)
Next j, i
.Cells(2, 1).Offset(, nc + 1).Resize(nc, nc) = a
.Cells(2, 1).Offset(, nc + 1).Resize(nc, nc).NumberFormat = "0.0000"
End With
End Sub

 

[shg]

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