求助：为何VBA代码执行耗时变长？数据量仅500行15列

核心问题分析 #

• 重复执行无效操作：代码中For Each self In Selection.SpecialCells(xlCellTypeConstants)循环里，每次都用With Selection对整个选中区域设置格式——相当于有多少个常量单元格，就重复设置多少次整个区域的格式，这是导致速度暴慢的根本原因。
• 滥用Select/Selection：ws.Select、Range(...).Select这类操作会触发Excel的界面交互逻辑，即使关闭了屏幕更新，底层仍有额外性能开销。
• 未声明变量：lastRow、self未显式声明，默认作为Variant类型处理，会增加内存占用和执行时间，还可能引发意外错误。
• 缺少错误处理：如果工作表中没有常量单元格，SpecialCells(xlCellTypeConstants)会直接抛出错误，中断执行。
• 未关闭额外Excel功能：仅关闭了DisplayAlerts和ScreenUpdating，没有禁用事件触发和自动计算，这些都会在后台消耗资源。

优化后的代码 #

Sub Cell_Formatting()
    ' 关闭更多影响性能的Excel功能
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim lastRow As Long
    Dim constCells As Range
    
    ' 遍历目标工作表
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "1", "2", "3", "4"
                lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                Set targetRange = ws.Range("A1:O" & lastRow)
                
                ' 处理常量单元格，避免无匹配时报错
                On Error Resume Next
                Set constCells = targetRange.SpecialCells(xlCellTypeConstants)
                On Error GoTo 0
                
                ' 仅在找到常量单元格时设置格式
                If Not constCells Is Nothing Then
                    With constCells
                        .Borders.Weight = xlThin
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                    End With
                End If
        End Select
    Next ws
    
    ' 恢复Excel默认设置
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

优化说明 #

• 移除Select/Selection：直接通过工作表对象引用单元格区域，避免界面交互开销。
• 一次性设置格式：找到所有常量单元格后，一次性对这些单元格设置格式，避免重复操作。
• 显式声明变量：指定变量类型（如Long、Range），提升执行效率和代码稳定性。
• 添加错误处理：捕获SpecialCells无匹配的情况，防止代码中断。
• 关闭额外功能：禁用事件触发和自动计算，减少后台资源消耗，操作完成后恢复默认设置。

内容的提问来源于stack exchange，提问作者Khaled Mohamed Rashed