VBA中Formula2与Formula的区别及数组公式应用咨询

一、Formula、FormulaR1C1、Formula2R1C1的核心差异 #

三个属性均用于设置/读取单元格公式，但适配的Excel功能与版本不同：

• Formula：采用A1引用样式，仅支持旧版非数组公式，无法兼容Excel 365/2021引入的动态数组函数（如FILTER、UNIQUE），设置动态数组公式时会报错或无法正常溢出。
• FormulaR1C1：采用R1C1引用样式，同样仅支持旧版公式，不兼容动态数组。R1C1样式中R[1]C[0]表示当前单元格下一行同列的相对引用，适合批量操作时灵活控制引用关系。
• Formula2R1C1：基于R1C1引用样式，专为动态数组函数设计，支持溢出数组的正确解析与渲染，是Excel录制动态数组相关宏时的默认选择，对应的A1样式版本为Formula2。

二、录制宏采用Formula2R1C1的原因 #

当使用FILTER这类动态数组函数时，Excel自动选择Formula2系列属性的核心原因：

1. 旧版Formula/FormulaR1C1无法处理动态数组的溢出特性，设置后要么无法生成结果，要么仅返回单个值；
2. Formula2R1C1原生支持动态数组的语法解析，能正确识别公式的溢出范围，确保宏录制的代码可复现手动操作效果；
3. R1C1引用样式在宏中更稳定，避免了A1样式中相对引用随单元格位置变化而混乱的问题。

三、不同公式类型的适用场景与选型建议 #

• 旧版兼容场景：若代码需适配Excel 2019及更早版本，且不使用动态数组函数，优先选Formula（A1样式，可读性高）或FormulaR1C1（批量操作灵活）。
• 动态数组场景：使用FILTER、XLOOKUP、UNIQUE等函数时，必须使用Formula2（A1）或Formula2R1C1（R1C1），否则无法实现溢出效果。
• 批量操作场景：需要循环设置多个公式、灵活控制相对引用时，选R1C1系列（FormulaR1C1或Formula2R1C1），相对引用写法更直观，比如R[-1]C表示当前单元格上一行同列。
• 可读性优先场景：若公式结构简单、无需批量调整引用，选A1样式的Formula或Formula2，代码更易理解。

四、FILTER数组公式的VBA代码优化（去除Select+避免溢出风险） #

优化前的典型冗余代码 #

Sub OldFilterCode()
    Sheets("Sheet1").Select
    Range("E2").Select
    ActiveCell.Formula2R1C1 = "=FILTER(R2C2:R100C3, R2C2:R100C2<>"""")"
End Sub

优化后的代码 #

Sub OptimizedFilterArray()
    Dim targetWs As Worksheet
    Dim dataRange As Range
    Dim outputStartCell As Range
    
    ' 定义工作表和关键范围（根据实际需求修改）
    Set targetWs = ThisWorkbook.Worksheets("Sheet1")
    Set dataRange = targetWs.Range("B2:C100") ' 数据源区域
    Set outputStartCell = targetWs.Range("E2") ' 公式起始单元格
    
    ' 直接设置动态数组公式，无需激活/选择单元格
    outputStartCell.Formula2R1C1 = "=FILTER(" & dataRange.Address(ReferenceStyle:=xlR1C1) & ", " & _
                                  dataRange.Columns(1).Address(ReferenceStyle:=xlR1C1) & "<>"""")"
    
    ' 可选：将动态溢出结果转为静态值，避免自动更新与溢出覆盖
    If outputStartCell.SpillToRange Is Not Nothing Then
        outputStartCell.SpillToRange.Value = outputStartCell.SpillToRange.Value
    End If
End Sub

代码说明 #

1. 去除Select/ActiveCell：通过直接引用Range对象操作，避免激活单元格带来的性能损耗和潜在错误（如工作表切换导致的引用混乱）；
2. 动态引用Range地址：用Address(ReferenceStyle:=xlR1C1)自动生成R1C1格式引用，无需手动硬编码单元格范围，提升代码可维护性；
3. 避免溢出风险：注释后的代码可将动态溢出结果转为静态值，防止后续数据源变化时自动更新，同时避免溢出范围意外覆盖其他单元格内容；
4. 兼容性：使用Formula2R1C1确保FILTER函数正常生成溢出数组，适配Excel 365/2021及以上版本。

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