CODEWITHSUNDEEP
excelvbauser-defined-functions
JoeJack
JoeJack

Reputation: 63

Excel VBA Function to cycle through range and measure multiple correlations

I'm trying to create an Excel VBA function that cycles through a cycle_range and calculates the correlation each range of 5 cell entries within that range to a base_range of 5 cell entries. The function should return the maximum correlation in the cycle_range. For example, the following data should return the 0.506..... output, as the second set of cycle range numbers, 7, 8, 9, 0, 8, have that correlation to the base range, which is the highest correlation:

cycle range     base range       output
     4              3          0.506253796
     7              7   
     8              3   
     9              2   
     0              9   
     8      
     5      
     4

The code I have thus far is below, it doesn't work. There is very clearly a problem with adding rng and elements together from cycle_range, but not sure what to do:

Function best_correl(correl_length As Double, base_range As Range, cycle_range As Range)


Dim i As Double
Dim rng As Range
Dim cycle_range_length As Double
Dim element As Variant
Dim max_correl As Double
Dim curr_correl As Double


cycle_range_length = cycle_range.Count - correl_length


For i = 1 To cycle_range_length

    For element = 1 To correl_length
        rng = rng + element
    Next element

    curr_correl = WorksheetFunction.Correl(base_range, rng)

    If curr_correl > max_correl Then
        max_correl = curr_correl
    End If

Next i


best_correl = max_correl


End Function

As always, any advice is very much appreciated, I'm having a tough time with this one. Thank you!

P.S. I cribbed some stuff from here - vba pass a group of cells as range to function

Upvotes: 0

Views: 244

Answers (2)

Tim Williams
Tim Williams

Reputation: 166511

Function best_correl(correl_length As Double, base_range As Range, cycle_range As Range)

    Dim c As Range
    Dim cycle_range_length As Long
    Dim max_correl As Double
    Dim curr_correl As Double, n As Long, pos as Long

    cycle_range_length = 1 + (cycle_range.Count - correl_length)
    n = 0
    pos = 0
    For Each c In cycle_range.Resize(cycle_range_length, 1).Cells
        n = n + 1             
        curr_correl = WorksheetFunction.Correl(base_range, c.Resize(correl_length, 1))
        If curr_correl > max_correl Then  
            max_correl = curr_correl
            pos = n  
        End If
    Next c

    'best_correl = max_correl '<< max correlation
    best_correl = pos         '<< position of max 

End Function

Upvotes: 2

urdearboy
urdearboy

Reputation: 14580

I think this is what you are looking for

Function best_correl(correl_length As Double, base_range As Range, cycle_range As Range)

Dim MyCell As Range
Dim max_correl As Double
Dim curr_correl As Double

For Each MyCell In cycle_range
    curr_correl = Application.WorksheetFunction.Correl(base_range, MyCell)
        If curr_correl > max_correl Then
            max_correl = curr_correl
        End If
Next MyCell

best_correl = max_correl


End Function

Upvotes: 1

Related Questions