为何调整去重逻辑后的VBA搜索函数执行速度反而大幅变慢

性能下降的核心原因 #

你遇到的性能暴跌本质是工作表单元格读写操作的调用次数被大幅放大，这是VBA开发中最常见的性能坑：Excel单元格的读写属于跨对象的IO操作，速度比纯内存数组操作慢3~4个数量级，单次调用耗时可以忽略，但调用次数翻上千倍后耗时会直接飙升。
具体对比两段代码的单元格操作次数：

• 原版逻辑：遍历N个搜索词，每个搜索词仅读取1次单元格取值，总读取次数=N次；写单元格的次数等于匹配命中次数（含重复命中的多写）
• 调整后逻辑：遍历M行文件内容，每行都要循环读取搜索词的单元格，总读取次数=M*平均每行匹配前遍历的搜索词数量。假设你有100个搜索词、10万行文件内容，就算平均每行遍历10个搜索词就命中，总读取次数也达到了100万次，是原版的1万倍，耗时自然暴涨。
  你可以对比两次运行的counter统计值，大概率调整后的总校验次数并没有比原版低多少，真正拖慢速度的是海量的单元格读取开销。

优化方案 #

只要把「反复读取工作表单元格」的操作改成一次性读取到内存数组即可，同时也可以优化结果写入逻辑，避免逐行写单元格进一步提升性能：

' 第一步：先把所有搜索词一次性读入内存数组，避免反复读工作表
Dim searchTerms As Variant
searchTerms = ActiveWorkbook.Sheets("SearchTerms").Range("A" & firstSearchTermRow & ":A" & ActiveWorkbook.Sheets("SearchTerms").Cells(Rows.Count, 1).End(xlUp).Row).Value
Dim termCount As Long
termCount = UBound(searchTerms, 1)

' 第二步：遍历行+匹配搜索词，所有操作都在内存中完成
Dim resultArr As Variant
ReDim resultArr(1 To UBound(bigStringArray), 1 To 1)
Dim resultCount As Long
resultCount = 0
Dim j As Long, t As Long
For j = 1 To UBound(bigStringArray) - 1
    For t = 1 To termCount
        counter = counter + 1
        If InStr(bigStringArray(j), searchTerms(t, 1)) <> 0 Then
            resultCount = resultCount + 1
            resultArr(resultCount, 1) = bigStringArray(j)
            Exit For ' 匹配到就跳过剩余搜索词，实现去重
        End If
    Next t
Next j

' 第三步：一次性把结果写入工作表，避免逐行写的开销
If resultCount > 0 Then
    Cells(resultsRow, 1).Resize(resultCount, 1).Value = resultArr
End If

这个优化后的版本既解决了重复输出的问题，所有核心操作都在内存完成，性能会比原版还要高3~10倍。

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