Excel VBA Function to measure cycle through range and measure multiple correlations

[joejackson123]

I'm trying to create an Excel VBA function that cycles through a <code>cycle_range</code> and calculates the correlation each range of 5 cell entries within that range to a <code>base_range</code> 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, <code>7, 8, 9, 0, 8</code>, have that correlation to the base range, which is the highest correlation:

<code>
</code>

<code>cycle range     base range       output
     4              3          0.506253796
     7              7   
     8              3   
     9              2   
     0              9   
     8      
     5      
     4  </code>

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:

<code>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</code>

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

 

[offthelip]

try this, I changed it to a subroutine to allow me to test it easily, also I wrote the answers out to see it was working:

Sub test2()
 Dim base_range As Range
 Dim cycle_range As Range
Set base_range = Range("B2:B6")
Set cycle_range = Range("A2:A9")
Dim inarr As Variant
Dim cycler As Variant
Dim temparr() As Variant


inarr = base_range
cycler = cycle_range
baselen = UBound(inarr)
cyclen = UBound(cycler)
ReDim temparr(1 To baselen, 1 To 1)


Dim i As Double
Dim max_correl As Double
Dim curr_correl As Double




Numits = cyclen - baselen






For i = 1 To Numits


     For j = 1 To baselen
      temparr(j, 1) = cycler(i + j - 1, 1)
     Next j
    curr_correl = WorksheetFunction.Correl(inarr, temparr)
     Cells(i + 1, 10) = curr_correl
    If curr_correl > max_correl Then
        max_correl = curr_correl
    End If


Next i




best_correl = max_correl




End Sub

 

[joejackson123]

Thanks offthelip, this works great!

 

[joejackson123]

Here's an alternate piece of code that I put together; this works. Now, though, my question is if I can have it return the LOCATION of the max_correl in the range - i.e., if the second iteration is the highest correlation, best_correl is returned as "2". Any help is appreciated, thanks!

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

    cycle_range_length = 1 + (cycle_range.Count - correl_length)

    For Each c In cycle_range.Resize(cycle_range_length, 1).Cells
        curr_correl = WorksheetFunction.Correl(base_range, c.Resize(correl_length, 1))
        If curr_correl > max_correl Then max_correl = curr_correl
    Next c

    best_correl = max_correl

End Function</code>

 

[offthelip]

If you used the code that I provided then all you need to do is save the value of the loop index I instead of the value e.g:
change this line:

max_correl = curr_correl

to

max_correl = i

with your code it is more difficult
but I suggest you try using something like (untested)

max_correl=c.row