我為一位同事創建了一個函式，它的作業原理類似于 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代碼來剖析和學習，但找不到。如果有人可以將我指向這些功能的源代碼，那也很棒。
uj5u.com熱心網友回復：
(a)如評論中所述，您可以通過它的Parent-Property獲取范圍的作業表
(b)在 VBA 中處理列字符幾乎沒有必要。
(c)小心你的變數宣告。如果你想在一行中宣告多個變數，你需要為每個變數指定型別，否則只有你指定型別的最后一個變數，所有其他變數都宣告為Variant. 參見例如https://stackoverflow.com/a/71250993/7599798
(d)子例程和函式之間幾乎沒有區別，只是函式回傳一個值。如果你想將該函式用作 UDF，你有一些（明顯的）限制，例如不要修改底層的 excel，不要使用Select和Activate- 但無論如何你都不應該在 VBA 中使用它。
看看下面的函式。出于速度原因，我將查找和回傳范圍讀入一個陣列，特別是對于 UDF 速度很重要，這減少了 Excel 和 VBA 之間的往返次數。我還將最后兩個引數設為可選。
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 will convert the first 3 parameters into Range-variables, and the Range always automatically belongs to one (and only one) Worksheet.
The first parameter (D2) will point to the cell D2 of the same sheet where the formula lives, and the next two parameters will point to column E and I of sheet Orders.
Don't be confused by the fact that Range.Address doesn't show the sheet name - a Range is not an address, it's an object that points to one or more cell on a worksheet.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/434844.html