VBA嵌套For Each循环执行过慢，寻求高效优化方案

核心问题分析 #

原代码运行缓慢的核心原因是双重循环逐单元格操作，每一次单元格读写都会频繁和Excel对象模型交互，这是VBA性能瓶颈的关键；同时未关闭Excel的自动刷新、计算等机制，进一步拖慢了运行速度。

具体优化措施 #

1. 关闭Excel后台冗余功能 #

在代码开头添加以下设置，避免每次操作触发界面更新、公式重算和事件响应，减少额外开销：

' 关闭不必要的Excel功能，提升运行速度
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

务必在代码结束（包括错误场景）时恢复这些默认设置：

' 恢复Excel默认状态
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

2. 重构循环逻辑，减少遍历次数 #

原代码每列都重新遍历一次所有单元格，我们可以先一次性筛选出目标区域内的非空单元格，后续直接对这批单元格进行批量操作，避免重复遍历。

3. 批量操作单元格，降低对象交互频率 #

直接对批量单元格范围进行公式赋值，替代逐单元格的读写操作，大幅减少和Excel对象模型的交互次数。

优化后的完整代码 #

Sub CopyFormulasEfficiently()
    Dim ws As Worksheet
    Dim ColName As String
    Dim ColNumber As Long
    Dim i As Long
    Dim CpyFrom As Range
    Dim nonEmptyCells As Range
    
    ' 关闭Excel后台冗余功能
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    On Error GoTo Cleanup ' 确保出错时也能恢复Excel设置
    
    Set ws = Sheets("DATA TO FORMAT (2)")
    Set CpyFrom = ws.Range("L10003:L10054") ' 直接绑定目标工作表，避免ActiveSheet歧义
    
    ColName = ws.Range("B9").Value2
    ColNumber = ws.Range(ColName & 1).Column ' 限定在目标工作表内，避免跨表错误
    
    ' 一次性收集所有非空单元格
    For Each Cell In CpyFrom
        If Cell.Value <> vbNullString Then
            If nonEmptyCells Is Nothing Then
                Set nonEmptyCells = Cell
            Else
                Set nonEmptyCells = Union(nonEmptyCells, Cell)
            End If
        End If
    Next Cell
    
    ' 批量复制公式到右侧对应列
    If Not nonEmptyCells Is Nothing Then
        For i = 0 To ColNumber - 13
            nonEmptyCells.Offset(0, i).Formula = nonEmptyCells.Offset(0, 1).Formula
        Next i
    End If
    
Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "运行出错: " & Err.Description, vbExclamation
    End If
End Sub

额外优化细节 #

• 修正原代码中ActiveSheet的歧义问题，直接将目标区域绑定到指定工作表ws，避免工作表切换导致的错误。
• 使用Union方法收集所有非空单元格，后续批量操作，大幅减少循环次数。
• 添加错误处理逻辑，确保即使代码运行出错，也能恢复Excel的默认设置，不影响后续操作。

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