如何优化VBA遍历区域执行InStr字符检查的运行性能

原有代码运行慢的核心原因有三个：逐单元格访问Range对象的属性开销极大、遍历过程中触发弹窗中断流程、没有关闭Excel的实时界面更新/自动计算等拖慢运行的功能。针对7.5万单元格的遍历场景，按以下方式优化后运行速度可提升100倍以上：

• 运行过程中临时关闭屏幕更新、自动公式计算、事件触发，跑完逻辑后统一恢复设置，避免每次单元格操作都触发界面重绘、公式重算
• 不要逐单元格读取Range对象，一次性将整个目标区域的数值读入内存数组，所有判断逻辑在内存中完成，内存遍历的速度远高于直接操作单元格对象
• 若仅需要判断区域内是否存在包含"-"的单元格，找到第一个匹配项后立刻退出循环，不需要遍历完所有单元格；不要在循环过程中弹提示框，弹窗会阻塞代码运行，所有提示逻辑放到遍历结束后统一执行
• 优先读取单元格的.Value属性而非.Text属性，.Text需要读取单元格格式化后的显示内容，开销远高于直接读取存储值

优化后的完整代码如下：

Sub Character_Check()
    Dim dataArr As Variant, cellVal As Variant
    Dim hasMatched As Boolean
    
    ' 临时关闭拖慢运行的Excel功能
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' 错误兜底，保证哪怕代码报错也能恢复Excel默认设置
    On Error GoTo RecoverSetting
    
    ' 一次性将目标区域值载入内存数组
    dataArr = Range("A1:C25000").Value
    hasMatched = False
    
    ' 遍历内存数组做判断
    For Each cellVal In dataArr
        If InStr(CStr(cellVal), "-") > 0 Then
            hasMatched = True
            Exit For ' 找到第一个匹配项直接终止遍历
        End If
    Next
    
    ' 遍历结束后统一输出结果
    If hasMatched Then MsgBox "True"

RecoverSetting:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

如果你的需求是找出所有包含"-"的单元格而非仅判断是否存在，也不要在循环内弹窗，可以先把匹配单元格的地址、行号等信息收集到变量中，等全部遍历完成后一次性输出结果，避免交互操作拖慢速度。

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