Excel宏运行繁忙时Autofill自动填充功能失效问题排查

问题背景 #

我编写了VBA宏用于获取工作表最后一行，并在参考列右侧两列自动填充公式。核心代码如下：

lastRowColumnU = sheet1.Cells(sheet1.Rows.Count, "U").End(xlUp).Row
If lastRowColumnU > 2 Then
    sheet1.Range("V2:W2").AutoFill Destination:= sheet1.Range("V2:W" & lastRowColumnU), Type:=xlFillCopy
End If

其中U列通过UNIQUE(FILTER(...))函数生成数据，该函数需在上述代码执行前完成计算。宏正常运行时会根据U列行数向下复制公式，但将宏嵌套在循环中批量处理指定路径下的.xlsm文件时，部分大文件（宏运行时长可达40分钟）出现自动填充失效的情况，代码逻辑未变却被跳过。怀疑是内存问题，求确保所有工作簿正常处理的方法。

完整相关代码：

Sub updateSheets(ByVal folderPath As String)
    Dim calculatorFiles As String
    Dim wb As Workbook
    tbdFiles = Dir(folderPath & "*.xlsm")
   
    Do While tbdFiles <> ""
      Set wb = Workbooks.Open(folderPath & tbdFiles, UpdateLinks:=False)
      If Not wb Is Nothing Then
          ' Update each sheet with its unique logic
          UpdateSheet1 wb
          UpdateSheet2 wb
          UpdateSheet3 wb
          UpdateSheet4 wb
          UpdateSheet5 wb
          UpdateSheet6 wb
          wb.RefreshAll
          wb.Close SaveChanges:=True
      End If
      tbdFiles = Dir 'Get the next file in the directory
    Loop
End Sub

Sub UpdateSheet3 (ByVal wb As Workbook)
    Dim wsFX As Worksheet
    Dim lastRowColumnU As Long
    Set wsFX = wb.sheets("3. FX")
    If Not wsFX Is Nothing Then
        lastRowColumnU = wsFX.Cells(wsFX.Rows.Count, "U").End(xlUp).Row
        lastRowColumnBJ = wsFX.Cells(wsFX.Rows.Count, "BJ").End(xlUP).Row

        If lastRowColumnU > 2 Then
             wsFX.Range("V2:W2").AutoFill Destination:= wsFX.Range("V2:W" & lastRowColumnU), Type:=xlFillCopy
        End If

        If lastRowColumnBJ > 2 Then
            wsFX.Range("BK2:BL2").AutoFill Destination:= wsFX.Range("BK2:BL" & lastRowColumnBJ), Type:=xlFillCopy
        End If
    End If
End Sub

可能的原因 #

• 公式计算延迟：大文件中的动态数组函数UNIQUE(FILTER(...))未在代码执行前完成计算，导致lastRowColumnU取到旧值（如2），触发跳过逻辑。
• 内存/资源耗尽：长时间循环处理大文件，Excel内存占用过高，导致AutoFill等操作因资源不足异常。
• AutoFill依赖失效：若V2/W2的公式依赖U列数据，U列未计算完成时，AutoFill无法正常执行。

解决方案 #

1. 强制等待公式计算完成 #

在获取最后一行前，确保Excel完成所有计算：

' 在UpdateSheet3获取lastRow前添加
' 等待全局计算完成
Do Until Application.CalculationState = xlDone
    DoEvents ' 释放系统资源，避免假死
Loop
' 再计算当前工作表确保数据最新
wsFX.Calculate

2. 优化内存与资源管理 #

• 循环中及时释放对象，避免内存泄漏：

' 在wb.Close后添加
wb.Close SaveChanges:=True
Set wb = Nothing ' 释放工作簿对象

• 关闭不必要的Excel功能，减少资源消耗：

' 在updateSheets开头添加
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual ' 手动计算，降低实时计算开销

' 循环结束后恢复设置
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

3. 替换AutoFill为直接写入公式 #

AutoFill在资源不足时易出问题，直接批量写入公式更稳定：

If lastRowColumnU > 2 Then
    ' 直接将V2/W2的公式批量写入到目标区域
    wsFX.Range("V2:V" & lastRowColumnU).Formula = wsFX.Range("V2").Formula
    wsFX.Range("W2:W" & lastRowColumnU).Formula = wsFX.Range("W2").Formula
End If

4. 增加错误捕获与日志记录 #

在关键步骤添加错误处理，记录失败文件信息，方便排查：

Sub UpdateSheet3(ByVal wb As Workbook)
    On Error GoTo ErrorHandler
    Dim wsFX As Worksheet
    Dim lastRowColumnU As Long, lastRowColumnBJ As Long
    Set wsFX = wb.Sheets("3. FX")
    If Not wsFX Is Nothing Then
        ' 等待计算完成
        Do Until Application.CalculationState = xlDone
            DoEvents
        Loop
        wsFX.Calculate
        
        lastRowColumnU = wsFX.Cells(wsFX.Rows.Count, "U").End(xlUp).Row
        lastRowColumnBJ = wsFX.Cells(wsFX.Rows.Count, "BJ").End(xlUp).Row

        If lastRowColumnU > 2 Then
             wsFX.Range("V2:V" & lastRowColumnU).Formula = wsFX.Range("V2").Formula
             wsFX.Range("W2:W" & lastRowColumnU).Formula = wsFX.Range("W2").Formula
        End If

        If lastRowColumnBJ > 2 Then
            wsFX.Range("BK2:BK" & lastRowColumnBJ).Formula = wsFX.Range("BK2").Formula
            wsFX.Range("BL2:BL" & lastRowColumnBJ).Formula = wsFX.Range("BL2").Formula
        End If
    End If
    Exit Sub
ErrorHandler:
    ' 写入错误日志
    Open "C:\macro_error_log.txt" For Append As #1
    Print #1, "文件: " & wb.FullName & " | 错误: " & Err.Description & " | 时间: " & Now()
    Close #1
End Sub

5. 拆分大文件处理 #

若单个文件运行时间过长，可分批次处理，避免Excel长时间处于高负载状态。

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