CODEWITHSUNDEEP
arraysexcelvba
Roy
Roy

Reputation: 13

Correlation of Two Arrays in VBA

everyone. I'm new to VBA, and recently ran into this issue that when I run the following function on two ranges, the excel gave a warning - "Run-Time error '1004': Method 'Correl' of object 'WorksheetFunction' failed." I'm wondering what's wrong in my case and how I can go around this warning. Thanks a lot.

'Calculate correlation coefficient with whatever sizes of two data sets
Function CorrelationDifferentSizes(data1, data2) As Double
    Dim length1 As Integer
    Dim length2 As Integer
    'length1 = UBound(data1) - LBound(data1) + 1
    'length2 = UBound(data2) - LBound(data2) + 1
    length1 = data1.Rows.Count
    length2 = data2.Rows.Count


Dim tmp1() As Variant
Dim tmp2() As Variant
ReDim tmp1(1 To length2)
ReDim tmp2(1 To length1)



If length1 > length2 Then
    Dim i As Integer
    Dim j As Integer
    For i = 1 To length2
        tmp2(i) = data2.Cells(i, 1)
    Next i
    For j = 1 To (length1 - length2)
        tmp2(length2 + j) = 0
    Next j

ElseIf length2 > length1 Then
    Dim m As Integer
    Dim n As Integer
    For m = 1 To length1
        tmp1(m) = data1.Cells(m, 1)
    Next m
    For n = 1 To (length2 - length1)
        tmp1(length1 + n) = 0
    Next n
End If

'Dim a1
'Dim a2
'a1 = Array(tmp1)
'a2 = Array(tmp2)

CorrelationDifferentSizes = Application.WorksheetFunction.Correl(tmp1, tmp2)
End Function

Upvotes: 1

Views: 3360

Answers (1)

user6432984
user6432984

Reputation:

You need determine the max rows count between the two ranges first and then ReDim the arrays to the max count. Declaring the array as a numeric type will prevent you from having to initiate the values to 0.

'Calculate correlation coefficient with whatever sizes of two data sets
Function CorrelationDifferentSizes(data1 As Range, data2 As Range) As Double
    Dim arr1() As Double, arr2() As Double, x As Long
    Dim Count As Long
    Count = Application.WorksheetFunction.Max(data1.Rows.Count, data2.Rows.Count)
    ReDim arr1(1 To Count)
    ReDim arr2(1 To Count)

    For x = 1 To data1.Rows.Count
        arr1(x) = data1(x)
    Next

    For x = 1 To data2.Rows.Count
        arr2(x) = data2(x)
    Next

    CorrelationDifferentSizes = Application.WorksheetFunction.Correl(arr1, arr2)
End Function

Upvotes: 2

Related Questions