vba - Find 方法在另一个子程序中调用时在 UDF 中有效，但在 Excel 表中使用时无效

Question

我正在编写一个代码，它将搜索一个值在一个范围内出现的特定位置。我的方法是根据输入在预定义的搜索范围上使用 Find 方法进行循环，并将每个查找结果添加到集合中。但是，当我在另一个子程序中调用 UDF 时，代码按预期工作，但是当放入 Excel 时，它返回 0，输入相同。我在 Excel 中通过 UDF 尝试了 F8，显然 Find 方法在 Excel 中使用时不返回任何内容，而当另一个子调用 UDF 时它正常工作。我已经包含了下面的代码。任何投入将不胜感激。

    Public Function CROSSEDVOLUME(dt As Variant, datarange As Range, date_col As Long, real_vol_col As Long, abs_vol_col As Long) As Double
    Dim filtered_arr() As Variant
    Dim i As Long, coll As Collection
    Dim ori_col As Long, ori_row As Long
    Dim find_res As Range, search_range As Range, first_occur As String

    ori_row = datarange.Cells(1, 1).Row
    ori_col = datarange.Cells(1, 1).column


    Set coll = New Collection

    'Setting search range
    Set search_range = Range(datarange.Cells(1, date_col), 
    datarange.Cells(datarange.Rows.count, date_col))

    If search_range.Cells(1, 1).Value = dt Then
        coll.Add 1
        first_occur = search_range.Cells(1, 1).Address
    End If

    Set find_res = search_range.Find(dt, search_range.Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False, False)

    'Code stucks here

    If find_res Is Nothing Then
        CROSSEDVOLUME = 0
        Exit Function
    Else
        If find_res.Address <> search_range.Cells(1, 1).Address Then
            first_occur = IIf(first_occur = "", find_res.Address, first_occur)
            coll.Add find_res.Row - ori_row + 1
            Set find_res = search_range.Find(dt, find_res, xlValues, xlWhole, xlByRows, xlNext, False, False)

            Do While Not find_res Is Nothing And find_res.Address <> first_occur
                coll.Add find_res.Row - ori_row + 1
                Set find_res = search_range.Find(dt, find_res, xlValues, xlWhole, xlByRows, xlNext, False, False)
            Loop
        End If
    End If

    CROSSEDVOLUME = coll.count

    End Function

标签： vba