Unable to get correl property of the worksheetfunc

Question

I am getting this error in VBA, when I try to get the correlation coefficient of two arrays Arr1 and Arr2

Dim arr1() As Variant
Dim arr2() As Variant

arr1 and arr2 are initialized in the function.

Dim result As Double
result = Application.WorksheetFunction.Correl(arr1, arr2)

The Correl method throws an exception

"unable to get correl property of the worksheetfunction class"

Any idea what could be the reason for the exception.

EDIT :

I think this problem occurs when one of the array has all the elements same. In this case, the correlation coefficient results in #DIV0 error.

Thanks

Answer 1

Like in many worksheet functions, arguments for Application.WorksheetFunction.Correl must have Range, NOT Variant type.

Additionally in Correl function standard deviation of any of these 2 ranges can't be 0.

Empty values instead of zeros can also sometimes cause this error.

Not following these rules, results in exactly the same error OP experienced.

Dim A as Range, B as Range
Dim myStDevA as Double, myStDevB as Double, myCorrel as Double

myStDevA = Application.WorksheetFunction.StDev(A)
myStDevB = Application.WorksheetFunction.StDev(B)

If myStDevA <> 0 and myStDevB <> 0 Then myCorrel = Application.WorksheetFunction.Correl(A,B)

Answer 2

I think you have forgotten to initialize your arrays.

This pretty simple example works:

Public Sub Test()
    Dim arr1() As Variant
    Dim arr2() As Variant
    Dim result As Double

    arr1() = Array(1, 2, 3, 4)
    arr2() = Array(1, 2, 3, 4)

    result = Application.WorksheetFunction.Correl(arr1, arr2)

    MsgBox ("The result is: " & result)
End Sub

Answer 3

Kiran,

Two possible solutions that prevent this error ... but seeing your data would be much more helpful

1. One (or both) your variant arrays contains strings rather than numbers
2. Your arrays are not equivalent in size