如何在用户定义的 VBA 函数中从另一个工作表中提取数据

Question

我为一位同事创建了一个函数，其工作方式类似于 xLookUp，但允许用户从找到的最后一个匹配项中返回 N。为了使该函数正常工作，我必须添加一个参数供用户输入 Worksheet.Name。如果没有这个参数，如果lookup_sheet不是输入函数的工作表，我就无法让函数从正确的工作表返回值。我了解 Application.Caller 方法可用于确保代码查看输入函数的工作表，但如何让它查看范围参数设置的工作表？

我的函数在名为“Supplies_List”的工作表中输入为 =xLookUp_X_From_Last (D2,"Orders",Orders!E:E,Orders!I:I,"",2) ，代码为：

Public Function xLookUp_X_From_Last(ByVal LookUp_Value As String, ByVal LookUp_Sheet As String, ByVal LookUp_Column As Range, ByVal Return_Column As Range, ifNA As String, Return_From_Last As Long) As String
    
    Dim myCol As Collection
    Dim i, LR, colCount, lColumn, rColumn, lookBack As Long
    Dim lLetter, cLetter, s As String
    Dim lSheet As Worksheet
    Dim wb As Workbook
    
    Set wb = ActiveWorkbook
    Set lSheet = wb.Worksheets(LookUp_Sheet)
    
    lookBack = Return_From_Last - 1
    
    If LookUp_Column.Columns.Count <> 1 Or Return_Column.Columns.Count <> 1 Then
        xLookUp_X_From_Last = "SELECTED RANGE ERROR"
        Exit Function
    End If
    If LookUp_Value = "" Then
        xLookUp_X_From_Last = ifNA
        Exit Function
    End If
    
    Set myCol = New Collection
    
    lColumn = LookUp_Column.Column
    rColumn = Return_Column.Column
    lLetter = Split(Cells(1, lColumn).Address, "$")(1)
    
    LR = lSheet.Range(lLetter & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
        If lSheet.Cells(i, lColumn).Value = LookUp_Value Then
            myCol.Add lSheet.Cells(i, rColumn).Value
        End If
    Next i

    colCount = myCol.Count
    
    If (colCount - lookBack) < 1 Then
        s = ifNA
    Else
        s = myCol(colCount - (lookBack))
    End If
    
    xLookUp_X_From_Last = s

End Function

即使 LookUp_Column 参数引用带有 Orders!E:E 的工作表，我也必须添加LookUp_Sheet 参数。我很擅长子例程，但不太擅长函数，这让我很困惑。

我到处寻找vLookUp和xLookUp的VBA代码来剖析和学习，但没有找到。如果有人能给我指出这些函数的源代码，那就太棒了。

Answer 1

(a) 正如评论中提到的，您可以通过其 Parent-Property
获取某个范围的工作表
(b) 在 VBA 中几乎不需要处理列字符。
(c) 请小心变量声明。如果你想在一行中声明多个变量，你需要为每个变量指定类型，否则只有最后一个变量是你指定的类型，其他所有变量都声明为Variant。请参阅示例 https://stackoverflow.com/a/71250993/7599798
(d) 子例程和函数之间几乎没有区别，只是函数返回一个值。如果您想将该函数用作 UDF，则有一些（明显的）限制，例如，不要修改底层 excel、不要使用 Select 和 Activate - 但是无论如何，你不应该在 VBA 中使用它。

看看下面的函数。出于速度原因，我将查找和返回范围读入数组，特别是对于 UDF 速度很重要，这减少了 Excel 和 VBA 之间的往返次数。我还将最后 2 个参数设置为可选。

Public Function xLookUp_X_From_Last(ByVal LookUp_Value As String, _
          ByVal LookUp_Column As Range, ByVal Return_Column As Range, _
          Optional ifNA As String = "not found", _
          Optional Return_From_Last As Long = 1) As String
    
    Dim myCol As Collection
    Dim i As Long, LR As Long, lookBack As Long
    
    lookBack = Return_From_Last - 1
    If LookUp_Column.Columns.Count <> 1 Or Return_Column.Columns.Count <> 1 Then
        xLookUp_X_From_Last = "SELECTED RANGE ERROR"
        Exit Function
    End If
    
    If LookUp_Value = "" Then
        xLookUp_X_From_Last = ifNA
        Exit Function
    End If
    
    Set myCol = New Collection
    
    Dim lookupValues As Variant, returnValues As Variant
    With LookUp_Column.Parent
        LR = .Cells(.Rows.Count, LookUp_Column.Column).End(xlUp).Row
        lookupValues = LookUp_Column.Cells(1, 1).Resize(LR, 1)
        returnValues = Return_Column.Cells(1, 1).Resize(LR, 1)
    End With

    For i = LBound(lookupValues) To UBound(lookupValues)
        If lookupValues(i, 1) = LookUp_Value Then
            myCol.Add CStr(returnValues(i, 1)), CStr(i)
        End If
    Next i

    If (myCol.Count - lookBack) < 1 Then
        xLookUp_X_From_Last = ifNA
    Else
        xLookUp_X_From_Last = myCol(myCol.Count - lookBack)
    End If
End Function

更新：您不需要Application.Caller。如果您将公式放入工作表中，就像

=xLookUp_X_From_Last (D2,Orders!E:E,Orders!I:I,"",2)

Excel 一样，会将前 3 个参数转换为 Range 变量，并且 Range 始终自动属于一个（且仅一个）工作表。
第一个参数 (D2) 将指向公​​式所在同一工作表的单元格 D2，接下来的两个参数将指向工作表 Orders 的 E 列和 I 列.
不要对 Range.Address 不显示工作表名称的事实感到困惑 - Range 不是地址，它是指向一个或多个的对象工作表上的单元格。