Excel VBA工作表Sum求和函数运行过慢，有哪些优化提速方案？

原代码核心低效原因 #

• 逐行读写单元格，VBA中与Excel工作表对象交互的操作是性能消耗的主要来源，原代码每行都要读13列单元格、写1次K列单元格，数据量大时耗时会线性飙升
• 循环内重复设置单元格样式，逐行修改格式触发多次屏幕渲染，冗余度极高
• 存在无意义的For Each cell In MyResultsRng循环，MyResultsRng本身就是单个单元格，该循环完全是多余开销
• 未关闭系统默认的屏幕更新、自动计算、事件触发机制，每次单元格修改都会触发重算和屏幕刷新，放大性能损耗

优化后代码 #

Sub Sum_multiple_columns_Optimized()
    Dim ws As Worksheet
    Dim destinationLastRow As Long
    Dim resultRng As Range
    
    Const FirstCol As Long = 12 ' "L"
    Const LastCol As Long = 24 ' "X"
    
    ' 关闭运行期间非必要的系统性能消耗项
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    Set ws = ThisWorkbook.Worksheets("Master")
    destinationLastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    ' 定位所有需要写入结果的K列范围
    Set resultRng = ws.Range("K5:K" & destinationLastRow)
    
    ' 批量写入求和公式，一次性完成所有行计算
    resultRng.FormulaR1C1 = "=SUM(RC" & FirstCol & ":RC" & LastCol & ")"
    ' 若不需要保留公式，要转为固定值，取消下一行注释即可
    ' resultRng.Value = resultRng.Value
    
    ' 一次性批量设置所有结果单元格的样式，无需逐行操作
    With resultRng
        .HorizontalAlignment = xlCenter
        .Font.Color = RGB(40, 101, 156)
        .Font.Bold = True
        .Font.Size = 9
        .Font.Name = "Calibri"
        .NumberFormat = "0.00"
    End With
    
    ' 恢复系统默认设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

优化效果说明 #

• 性能提升可达原代码的数十到数百倍，数据量越大提升效果越明显
• 移除了所有无意义循环，用Excel原生公式批量计算，效率远高于VBA逐行遍历求和
• 所有单元格读写、样式设置都改为批量一次性操作，大幅减少工作表交互次数
• 运行期间关闭非必要的系统触发逻辑，避免冗余开销

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