Search code examples
excelvbaarray-formulas

Use Excel VBA Evaluate Method to Return/Analyze Moving Array At Every Row Over Range

The VBA evaluate method can be tricky for array formulas in that many Excel functions don't natively accept arrays as arguments, but it is very useful as it speeds up processing time over loops or spreadsheet formulas by orders of magnitude. For example, I've automated the alignment of signals (several hundred thousand rows) captured by separate data loggers with the use of the EVALUATE("INDEX(IF(1,N(OFFSET(...)") idiom over several columns to resample and ultimately do a correlation sweep across the signals. Doing this with VBA Evaluate takes seconds rather than the hours it took to fill down, then calculate the formulas, then manually move data around...

I am now trying to use the Evaluate method over a large range of data to speed up a peak finding algorithm. The problem I'm having is that, with both VBA Evaluate and spilling array formulas directly in the spreadsheet, only the first calculated value or overall max is returned for each row in the range. I believe this is similar to many other questions I've read on how to use Evaluate, but I can't figure out how to apply the solutions to my case.

The attached image shows an example of what I'm basically trying to accomplish using a random signal in column B and spreadsheet formulas filled down through the range in columns C and D. In the example, I have identified if each sample in the signal is a local peak over a +/-100 ms (i.e., 5 sample) range. I included two different formulas in columns C and D to identify the peaks to illustrate multiple generic approaches:

Filled down in Column C: "= B3 = MAX(OFFSET(B$1,ROW()-3,0,5))" Filled down in Column D: "= B3 = MAX(FILTER(B$3:B$51,(ROW(B$3:B$51)>=ROW() - 2)*(ROW(B$3:B$51)<=ROW()+2)))"

Sample Peak Detection

1

I can't recall all the variations I've tried to get this to work with a single Evaluate operation in VBA, but following is a small sample. As with the generic example in the image above, assume the signal data is in column B and we're identifying peaks in column C. Rather than explicitly identifying whether a sample is a peak, the formulas below are simply trying to return the peak for +/- 2 samples from each row.

Sub EvalXmpl()

Dim LR as Long 'Last row

LR = .Range("A" & .Rows.count).End(xlUp).Row

With Worksheets(1)

  ' Example 1 - Returns MAX of overall range for every row
  .Range("C3:C" & LR) = .Evaluate("INDEX(IF(1,MAX(N(OFFSET(B1,ROW(C3:C" & LR & ") - 3,0,5)))),)")

  ' Example 2 - Returns MAX of overall range for every row
'  .Range("C3:C" & LR) = .Evaluate("IF(ROW(3:" & LR & "),MAX(N(OFFSET(B1,ROW(3:" & LR & ") - 3,0,5))))")

  ' Example 3 - Returns MAX of first calculation for every row
'  .Range("C3:C" & LR) = .Evaluate("INDEX(IF(1,MAX(FILTER(B3:B" & LR & ",(ROW(3:" & LR & _
      ") >= ROW()-2)*(ROW(3:" & LR & ") <= ROW()+2)))),)")

End With

End Sub

I tried variations on all of those to see if I could force the arrays to return correctly. There are two things I think may be hanging me up

  1. Forcing an array formula via INDEX() or IF(ROW()) when Evaluate returns a single value for each row works fine, but since I'm returning a moving range at each row forcing isn't working the same.
  2. MAX natively accepts an array so it's somehow negating the forcing?

Some resources

Solution

  • Running Peaks

    Hardcoded

    Sub RunningPeaks()
    With ThisWorkbook.Worksheets(1)
        With .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
            .EntireRow.Columns("C").Value = .Worksheet.Evaluate( _
                "BYROW(" & .Address & ",LAMBDA(r,r=MAX(OFFSET(r,-2,0,5))))")
        End With
    End With
End Sub