VBA调用WorksheetFunction出现运行时错误1004，求错误处理方案

问题原因 #

你的代码崩溃是因为WorksheetFunction.Search在找不到匹配内容时会直接抛出运行时错误，而非返回False，导致代码中断。另外还有个隐藏问题：每次处理新单元格时没清空存储位置的集合，会遗留上一个单元格的数据干扰当前判断。

解决方案1：用错误处理跳过匹配失败场景 #

通过开启错误捕获，让代码在匹配失败时自动跳过，继续执行下一个关键词的判断：

Sub IdentifyCameraPresence()
    Application.ScreenUpdating = False
    
    Dim Rng As Range
    Dim i As Long, m As Long, j As Long, k As Long, n As Long ' 索引用Long更合适
    Dim Negatives As Object, Keys As Object
    Dim NWords As Object, KWords As Object
    Dim Temp As Variant
    
    Set Negatives = CreateObject("System.Collections.ArrayList")
    Set Keys = CreateObject("System.Collections.ArrayList")
    
    Set NWords = CreateObject("System.Collections.ArrayList")
    NWords.Add "no"
    NWords.Add "not"
    
    Set KWords = CreateObject("System.Collections.ArrayList")
    KWords.Add "key1"
    KWords.Add "key2"
    KWords.Add "key3"
    KWords.Add "key4"
    KWords.Add "key5"

    For Each Rng In Selection
        ' 每次处理新单元格前清空集合，避免旧数据干扰
        Negatives.Clear
        Keys.Clear
        
        Temp = Split(Rng.Value)
        For i = 0 To UBound(Temp)
            ' 匹配否定词
            For m = 0 To NWords.Count - 1
                If Temp(i) = NWords(m) Then
                    Negatives.Add i
                    Exit For ' 找到匹配就跳出循环，减少冗余判断
                End If
            Next m
            
            ' 匹配关键词，添加错误处理
            For j = 0 To KWords.Count - 1
                On Error Resume Next ' 开启错误捕获
                Application.WorksheetFunction.Search(KWords(j), Temp(i))
                If Err.Number = 0 Then ' 无错误=匹配成功
                    Keys.Add i
                    Exit For
                End If
                On Error GoTo 0 ' 关闭错误捕获
            Next j
        Next i
        
        ' 检查否定词与关键词的距离并高亮
        For k = 0 To Keys.Count - 1
            For n = 0 To Negatives.Count - 1
                If Abs(Negatives(n) - Keys(k)) < 5 Then
                    Rng.Interior.ColorIndex = 35
                    Exit For ' 只要有一组符合就高亮，无需继续检查
                End If
            Next n
        Next k
    Next Rng
    
    Application.ScreenUpdating = True
End Sub

解决方案2：用VBA内置InStr函数替代（更推荐） #

InStr找不到匹配时返回0，不会报错，还能通过vbTextCompare实现不区分大小写的匹配（和Search行为一致），代码更简洁健壮：

Sub IdentifyCameraPresence()
    Application.ScreenUpdating = False
    
    Dim Rng As Range
    Dim i As Long, m As Long, j As Long, k As Long, n As Long
    Dim Negatives As Object, Keys As Object
    Dim NWords As Object, KWords As Object
    Dim Temp As Variant
    
    Set Negatives = CreateObject("System.Collections.ArrayList")
    Set Keys = CreateObject("System.Collections.ArrayList")
    
    Set NWords = CreateObject("System.Collections.ArrayList")
    NWords.Add "no"
    NWords.Add "not"
    
    Set KWords = CreateObject("System.Collections.ArrayList")
    KWords.Add "key1"
    KWords.Add "key2"
    KWords.Add "key3"
    KWords.Add "key4"
    KWords.Add "key5"

    For Each Rng In Selection
        Negatives.Clear
        Keys.Clear
        
        Temp = Split(Rng.Value)
        For i = 0 To UBound(Temp)
            ' 匹配否定词
            For m = 0 To NWords.Count - 1
                If Temp(i) = NWords(m) Then
                    Negatives.Add i
                    Exit For
                End If
            Next m
            
            ' 用InStr匹配关键词，vbTextCompare不区分大小写
            For j = 0 To KWords.Count - 1
                If InStr(1, Temp(i), KWords(j), vbTextCompare) > 0 Then
                    Keys.Add i
                    Exit For
                End If
            Next j
        Next i
        
        ' 检查距离并高亮
        For k = 0 To Keys.Count - 1
            For n = 0 To Negatives.Count - 1
                If Abs(Negatives(n) - Keys(k)) < 5 Then
                    Rng.Interior.ColorIndex = 35
                    Exit For
                End If
            Next n
        Next k
    Next Rng
    
    Application.ScreenUpdating = True
End Sub

关键修改点 #

1. 用Long替代Double作为索引变量，符合VBA整数使用习惯，避免无效类型转换
2. 新增集合清空逻辑，确保每个单元格的判断独立
3. 匹配到目标词后添加Exit For，减少冗余循环
4. 方案2用InStr替代WorksheetFunction.Search，无需错误处理，代码更稳定

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