testing for correlation matrix

[szesz]

Hi All,

Can someone help me with this?

The task is to write a function in VBA that determines about a given range whether it can be a correlation matrix (i.e. whether the range is a square form, whether it has all values as numbers, whether the diagonal has only values of 1, whether the matrix is symmetrical, and whether the values of the matrix are in the [-1; 1] interval). I have thought of doing this with several IF commands, but I am unsure whether that's the best solution... this is probably very easy to do but I have only started learning VBA a few weeks ago so I am not really confident yet. Thanks for any help! in advance!!!

[Paul_Hossler]

Demonstrates some techniques

Not the most efficient method, and does not indicate which error or where the error occurred if there is one



Option Explicit

'The task is to write a function in VBA that determines about a given range whether it can be a correlation matrix

Function IsCorrelation(r As Range) As Boolean
Dim iRow As Long, iCol As Long

'set default exit value
IsCorrelation = False

With r
'whether the range is a square form,
If .Rows.Count <> .Columns.Count Then Exit Function

'whether it has all values as numbers
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
If Not IsNumeric(.Cells(iRow, iCol).Value) Then Exit Function
Next iCol
Next iRow

'whether the diagonal has only values of 1
For iRow = 1 To .Rows.Count
iCol = iRow
If .Cells(iRow, iCol).Value <> 1# Then Exit Function
Next iRow

'whether the matrix is symmetrical
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
If .Cells(iRow, iCol).Value <> .Cells(iCol, iRow).Value Then Exit Function
Next iCol
Next iRow

'and whether the values of the matrix are in the [-1; 1] interval)
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
If Abs(.Cells(iRow, iCol).Value) > 1# Then Exit Function
Next iCol
Next iRow
End With

IsCorrelation = True

End Function

[szesz]

Thank you so much, this makes it quite clear (and I am happy that my original thought process was not that far from this :) )