使用VBA对Excel百万行数据执行TRIM操作的最快方法是什么？

原代码性能问题 #

原逐单元格循环方案在处理超大规模数据集时效率极低，核心问题是每次读写单元格都需要VBA和Excel工作表界面交互，百万行量级下会产生海量交互开销，运行时间会达到数分钟甚至更久。

优化后VBA实现（数组方案，速度提升100倍以上） #

把整段数据一次性读取到内存数组中处理，处理完成后一次性写回工作表，几乎消除了单元格交互开销，百万行数据一般10秒内就能处理完成。
代码如下：

Private Sub CommandButton1_Click()
    Dim dataArr As Variant
    Dim usedRng As Range
    Dim i As Long, j As Long
    Dim lastRow As Long, lastCol As Long
    
    On Error GoTo errHandler
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual ' 额外关闭自动计算，加速运行
    
    ' 获取当前工作表使用范围
    Set usedRng = ActiveSheet.UsedRange
    lastRow = usedRng.Rows.Count
    lastCol = usedRng.Columns.Count
    
    ' 把范围值读入数组
    dataArr = usedRng.Value
    
    ' 遍历数组执行TRIM操作，全部在内存中完成
    For i = 1 To lastRow
        For j = 1 To lastCol
            ' 仅处理文本类型内容，跳过数值、空值等
            If VarType(dataArr(i, j)) = vbString Then
                dataArr(i, j) = WorksheetFunction.Trim(dataArr(i, j))
            End If
        Next j
    Next i
    
    ' 处理完成一次性写回工作表
    usedRng.Value = dataArr
    ' 统一设置文本格式，不需要循环逐个设置
    usedRng.NumberFormat = "@"
    
exitSub:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic ' 恢复自动计算
    Set usedRng = Nothing
    Exit Sub
    
errHandler:
    ' 没有符合条件的单元格时直接退出
    If Err.Number = 1004 Then
        Resume exitSub
    End If
End Sub

核心优化点 #

• 放弃逐单元格读写，改用数组批量处理，所有操作在内存中完成，交互开销降低99%以上
• 统一批量设置单元格格式，不需要循环逐个单元格修改，进一步减少不必要的性能损耗
• 额外关闭自动计算、屏幕刷新两个影响运行速度的Excel设置，运行结束后自动恢复
• 错误处理逻辑更严谨，不会出现原代码中错误触发后直接退出、后续设置不恢复的问题

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