Excel VBA UDF从单元格调用时FindNext出错仅返回首个匹配值

核心原因 #

从Excel单元格调用UDF时，FindNext 方法无法继承此前Find操作的搜索上下文，因此永远返回空值，这是UDF运行在工作表受限上下文的固有特性，和代码逻辑无关。

解决方案 #

将循环中的FindNext替换为显式调用Find方法，每次都传入完整的搜索参数，仅修改after参数为上一次找到的单元格即可。

修正后的完整代码如下：

Option Explicit
Function MatchAllInCol(what As String, inCol As Range) As Variant
' 查找指定列'inCol'中所有匹配'what'的单元格
' 返回匹配项相对于列起始位置的下标数组
' 无匹配时返回UBound为-1的空数组
' 输入参数'inCol'必须为单列范围
On Error GoTo debug_:
MatchAllInCol = Split("") ' 调用Split返回UBound为-1的空数组
If inCol.Columns.Count <> 1 Then
    Exit Function
End If
Dim found As Range, lastCell As Range
' 从最后一个单元格之后开始搜索，保证首个匹配为列第一个符合条件的单元格
Set lastCell = inCol.Cells(inCol.Rows.Count, 1)
Set found = inCol.Find(what:=what _
    , LookIn:=xlValues _
    , LookAt:=xlWhole _
    , after:=lastCell _
    , SearchOrder:=xlByRows _
    , SearchDirection:=xlNext _
    , MatchCase:=False _
    , MatchByte:=True _
    )
If found Is Nothing Then Exit Function
Dim firstAddr As String
firstAddr = found.Address
Dim matches As String
matches = found.Row - inCol.Row + 1

Const delim As String = "＠" ' 该字符不能出现在搜索列的内容中
Do
    ' 替换FindNext为显式调用Find，每次传入完整参数保证上下文正确
    Set found = inCol.Find(what:=what _
        , LookIn:=xlValues _
        , LookAt:=xlWhole _
        , after:=found _
        , SearchOrder:=xlByRows _
        , SearchDirection:=xlNext _
        , MatchCase:=False _
        , MatchByte:=True _
        )
    If found Is Nothing Then Exit Do
    If found.Address = firstAddr Then Exit Do
    matches = matches & delim & found.Row - inCol.Row + 1
Loop

If Len(matches) <> 0 Then MatchAllInCol = Split(matches, delim)
Exit Function
debug_:
    Debug.Print "ERROR:" & Err.Description & " from " & Err.Source
    Stop
End Function

Sub MatchAllInCol_TEST()
' VBA侧测试代码，可正常运行
    Dim rng As Range, res As Variant
    Set rng = ActiveSheet.Range("B2:B7")
    res = MatchAllInCol("b", rng)
    Dim ii As Long
    If UBound(res) = -1 Then MsgBox ("nothing found") _
    Else MsgBox ("found: " & Application.TextJoin(", ", False, res))
End Sub

微软官方示例死循环问题说明 #

该示例死循环的原因是缺失了回到首个匹配地址的退出判断。Find方法遍历完所有单元格后会回到搜索起点，永远不会返回Nothing，因此仅判断Not c Is Nothing会导致循环永远执行。正确的循环退出条件需要加上地址判断，修正后的示例如下：

Sub FindValue()
    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500")
        Set c = .Find(2, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = 5
                Set c = .FindNext(c)
            ' 增加地址判断，回到首个匹配位置时退出
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub

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