VBA中For循环遍历Excel匹配文件效率低的优化方法

API 探路者

2026-8-28

问题原因

原代码效率极低的核心问题有两个：

采用了双层嵌套循环，总操作量达到30000 * 68509 ≈ 20.5亿次，计算量级过大
全程逐单元格读写工作表数据，没有利用内存数组，Excel每次读写单元格都会触发底层界面、计算相关的额外逻辑，资源开销极高
按这个思路优化后，整个匹配过程耗时不会超过5秒。

优化方案

核心思路是反转匹配逻辑，砍掉无意义的嵌套循环，最大化减少和工作表的直接交互：

先一次性遍历文件夹内所有PDF，提取6位ID存入字典（哈希表结构，查询匹配的时间复杂度为O(1)，不需要循环遍历匹配）
把BC列所有CCNumber数据一次性读入内存数组，全程在内存中完成匹配判断
所有匹配结果先存储在内存数组中，最后一次性批量写入BD列，避免逐单元格写值的开销
运行时临时关闭自动计算、事件触发，进一步压缩不必要的系统消耗

优化后可直接运行的代码如下：

Sub FastMatchFiles()
    ' 不想手动加引用可以直接用下方后期绑定写法替换这两行
    ' Dim idDict As Object
    ' Set idDict = CreateObject("Scripting.Dictionary")
    Dim idDict As New Dictionary
    Dim fileName As String, targetPath As String
    Dim lastRow As Long, i As Long
    Dim dataArr As Variant, resultArr As Variant
    Dim currID As String
    
    ' 基础配置
    targetPath = "Some\Directory\Here\" ' 路径末尾记得补反斜杠
    lastRow = 68512 ' 也可以改成动态获取最后一行：Cells(Rows.Count, "BC").End(xlUp).Row
    
    ' 临时关闭非必要功能
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' 第一步：遍历所有PDF，将ID存入字典
    fileName = Dir(targetPath & "*.pdf")
    Do While fileName <> ""
        currID = Left(fileName, 6)
        ' 自动去重，同一ID对应多个文件也只存一次
        If Not idDict.Exists(currID) Then idDict.Add currID, "Available"
        fileName = Dir
    Loop
    
    ' 第二步：将BC列数据一次性读入内存
    dataArr = Range("BC4:BC" & lastRow).Value
    ' 初始化同尺寸的结果数组
    ReDim resultArr(1 To UBound(dataArr, 1), 1 To 1)
    
    ' 第三步：内存中完成全部匹配判断
    For i = 1 To UBound(dataArr, 1)
        currID = Left(Trim(CStr(dataArr(i, 1))), 6)
        resultArr(i, 1) = IIf(idDict.Exists(currID), "Available", "Not Found")
    Next i
    
    ' 第四步：结果一次性批量写回BD列
    Range("BD4:BD" & lastRow).Value = resultArr
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    MsgBox "匹配完成，共识别到" & idDict.Count & "个对应文件ID"
End Sub

手动添加Scripting Runtime引用的方法：打开VBA编辑器→点击顶部菜单「工具」→「引用」→找到Microsoft Scripting Runtime勾选确认即可。不想手动操作直接用注释里的后期绑定写法即可，不需要额外配置。

补充说明

优化后总操作量从20亿次降到了3万次文件遍历 + 6.5万次数组遍历，总操作量不到10万次，和原写法效率差了4个数量级
全程仅做两次工作表交互（读BC列、写BD列），完全避免了逐单元格操作的额外开销
如果文件夹内存在前6位不是数字的异常文件名，可以在提取ID后加一层数值校验，避免运行报错

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