如何借助数组优化实现VBA For Loop循环提速

逐单元格读写工作表是VBA运行效率低的核心原因，每一次调用Range取值、赋值都会触发和工作表的交互，数据量较大时开销极高。通过数组将批量数据读入内存完成计算，再一次性写回结果，可将运行效率提升数十倍至上百倍，以下是优化后可直接运行的代码：

Sub DetectedCheck()
    Dim ws As Worksheet
    Dim reconrows As Long, i As Long
    Dim arrCheckData As Variant, arrResult As Variant
    
    ' 绑定目标工作表，减少重复对象调用
    Set ws = ActiveWorkbook.Sheets(1)
    With ws
        ' 统计有效数据行数
        reconrows = .Range("A" & .Rows.Count).End(xlUp).Row
        ' 一次性将需要判断的J、K列所有数据读入内存数组
        arrCheckData = .Range("J2:K" & reconrows).Value
        ' 初始化结果数组，行数和待判断数据完全对齐
        ReDim arrResult(1 To UBound(arrCheckData, 1), 1 To 1)
    End With
    
    ' 内存中完成全部判断逻辑，无工作表交互开销
    For i = 1 To UBound(arrCheckData, 1)
        If arrCheckData(i, 1) <> "Not Found" And arrCheckData(i, 2) <> "" Then
            arrResult(i, 1) = "No Issue"
        End If
    Next i
    
    ' 一次性将所有结果批量写回S列
    ws.Range("S2:S" & reconrows).Value = arrResult
End Sub

核心优化点说明 #

• 提前将工作表对象赋值给变量，避免循环中重复调用ActiveWorkbook.Sheets(1)产生冗余开销
• 批量读取待判断列数据：仅执行1次读操作就把J、K列所有待校验数据加载到内存，替代原循环中逐行读2次单元格的逻辑
• 内存中完成逻辑判断：循环过程全程在内存数组中运算，没有任何工作表读写操作，运算速度极快
• 批量写入结果：仅执行1次写操作就把所有判断结果写回S列，替代原循环中逐行写单元格的逻辑
• 修正原代码注释矛盾：原注释标注写入Q列，实际操作的是S列，优化版注释与实际逻辑完全对齐

注：如果数据量仅几十行，数组优化的感知不强；当数据量达到上千、上万行时，优化前后的速度差会非常明显，可避免Excel出现卡屏、无响应的情况。

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