如何在VBA中计算结果而非写入FormulaR1C1公式以提升性能

核心优化思路 #

• 放弃向单元格写入公式的方式，改用VBA直接计算结果后写入单元格值，彻底避免公式留存触发重复计算
• 利用Application.Evaluate或WorksheetFunction完成内部计算，降低工作表的计算负载
• 批量操作单元格（通过数组写入），减少VBA与工作表的交互次数，大幅提升执行效率

代码示例（替换原FormulaR1C1写法） #

假设原代码是通过Worksheet_Calculate触发，向Sheet2写入6组公式，优化后的代码如下：

Private Sub Worksheet_Calculate()
    Dim calcArray As Variant
    Dim targetRange As Range
    
    ' 关闭不必要的Excel功能，提速
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' --------------------------
    ' 第一组计算：示例为计算Sheet1每行B-D列的和，写入Sheet2的A1:A300000
    ' --------------------------
    Set targetRange = Sheet2.Range("A1:A300000")
    ' 用Evaluate批量计算，返回结果数组
    calcArray = Application.Evaluate("Sheet1!B1:B300000+Sheet1!C1:C300000+Sheet1!D1:D300000")
    ' 直接将数组写入目标区域
    targetRange.Value = calcArray
    
    ' --------------------------
    ' 第二组计算：示例为计算Sheet1每行E-G列的平均值，写入Sheet2的B1:B300000
    ' --------------------------
    Set targetRange = Sheet2.Range("B1:B300000")
    calcArray = Application.Evaluate("AVERAGE(Sheet1!E1:G300000)")
    targetRange.Value = calcArray
    
    ' --------------------------
    ' 其余4组计算按上述逻辑补充，替换为你的实际计算需求
    ' --------------------------
    
    ' 恢复Excel功能
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

关键注意事项 #

• 批量计算优先：尽量用Application.Evaluate返回数组，避免逐个单元格循环计算，这是提升速度的核心
• 自定义逻辑处理：如果计算逻辑无法用单个工作表函数实现，可编写高效的VBA自定义函数，再通过Evaluate调用；或直接在VBA中处理数组（内存中运算远快于单元格读写）
• 数据源引用准确：Evaluate中的单元格引用需与实际数据源匹配，跨表引用要明确工作表名称
• 错误处理：若计算可能出现错误，可添加WorksheetFunction.IfError包裹计算逻辑，避免程序崩溃

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