Excel VBA Evaluate逐行计算移动范围最大值异常如何解决？

背景 #

VBA的Evaluate方法虽因多数Excel函数不原生支持数组参数存在使用门槛，但相比循环或手动填充工作表公式，处理速度能提升数个数量级。例如曾用EVALUATE("INDEX(IF(1,N(OFFSET(...))")语法完成数十万行多数据记录仪信号的对齐、重采样及相关性扫描，仅需数秒，而原手动填充公式的方法耗时数小时。

问题场景 #

现需用该方法加速峰值检测算法，但遇到核心问题：无论是通过VBA Evaluate执行，还是直接在工作表输入溢出数组公式，目标范围内每行仅返回首个计算值或整体最大值，而非每行对应±2样本移动范围的最大值。

工作表示例：

• B列为随机信号数据
• C列通过填充公式识别局部峰值：= B3 = MAX(OFFSET(B$1,ROW()-3,0,5))
• D列通过另一种填充公式实现相同逻辑：= B3 = MAX(FILTER(B$3:B$51,(ROW(B$3:B$51)>=ROW() - 2)*(ROW(B$3:B$51)<=ROW()+2)))

尝试过的无效VBA代码 #

Sub EvalXmpl()
    Dim LR As Long 'Last row
    LR = Worksheets(1).Range("A" & Worksheets(1).Rows.Count).End(xlUp).Row
    
    With Worksheets(1)
        ' Example 1 - 所有行返回整个范围的最大值
        .Range("C3:C" & LR) = .Evaluate("INDEX(IF(1,MAX(N(OFFSET(B1,ROW(C3:C" & LR & ") - 3,0,5)))),)")
        
        ' Example 2 - 所有行返回整个范围的最大值
        '.Range("C3:C" & LR) = .Evaluate("IF(ROW(3:" & LR & "),MAX(N(OFFSET(B1,ROW(3:" & LR & ") - 3,0,5))))")
        
        ' Example 3 - 所有行返回首次计算的最大值
        '.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

问题原因推测 #

1. 用INDEX()或IF(ROW())强制数组公式时，仅在返回单个值的场景可行，处理移动范围计算时逻辑失效；
2. MAX函数原生支持数组输入，可能抵消了强制数组运算的效果，导致全局最大值被返回。

需求 #

寻求正确使用VBA Evaluate方法，实现逐行计算对应±2样本移动范围最大值的解决方案。

内容的提问来源于stack exchange，提问作者tgdemas