我正在改进我的Excel表，以跟踪我共享的威士忌桶，但我遇到了一个奇怪的问题，至少对我来说很奇怪。
我创建的barkinfoBySKU函数起作用，基本上是INDEX MATCH(行(MATCH(列(函数。
第二个函数，barkinfoBySKU_DYN也可以工作，但无法显示它应该从barkinfobySKU函数中获得的结果。barkinfoBySKU_DYN根据付款类型(初始和最终(，为某些列排序哪些列名需要与barkinfobySKU函数一起使用。
我不是经常用VBA编程，在寻找解决方案一周后，我目前正忙于实现这一目标。
有人看到我哪里错了吗？
我确实怀疑问题出在我如何调用原始函数上。。。我在搜索过程中尝试了几种方法，但不幸的是，没有成功：/
我用下面的VBA代码和一些测试数据创建了一个Excel文档的修剪副本。它可以从我的Google Drive下载。
(当然，当你看到让VBA代码更好/更快的方法时，也可以随时向我指出。(
Public Function caskinfoBySKU(SKU, colname) As Variant
On Error GoTo CleanFail
Dim indexResult As Variant
Dim rowMatch, colMatch As Long
SKU = Trim(SKU)
colname = Trim(colname)
If SKU = "" Or colname = "" Then
GoTo CleanFail
End If
'in which row do we find the SKU
rowMatch = WorksheetFunction.Match(SKU, Range("tbl_caskinfo[SKU]"), 0)
'is rowMatch an integer?
If Int(rowMatch) = rowMatch Then
'in which column do we find the colname
colMatch = WorksheetFunction.Match(colname, Range("tbl_caskinfo[#headers]"), 0)
'is colMatch an integer?
If Int(colMatch) = colMatch Then
'get result
indexResult = WorksheetFunction.Index(Range("tbl_caskinfo"), rowMatch, colMatch)
If Not indexResult = "" Then
GoTo ReturnResult
Else 'no result found
GoTo CleanFail
End If
Else 'colname not found in colnames row
GoTo CleanFail
End If
Else 'SKU not found in SKU column
GoTo CleanFail
End If
CleanFail:
caskinfoBySKU = "•" 'ALT+0149
Exit Function
ReturnResult:
'MsgBox rowMatch & "," & colMatch & ": " & indexResult
caskinfoBySKU = indexResult
End Function
和
Public Function caskinfoBySKU_DYN(key, payment, SKU) As Variant
' redirect to Est. Yield / Act. Yield column or Initial Payment / Act. Final Payment
On Error GoTo CleanFail
Dim colname As Variant
payment = Trim(payment)
If payment = "" Then
GoTo CleanFail
End If
Select Case UCase(key)
Case "YIELD"
Select Case LCase(payment)
Case "initial"
colname = "Est. Yield"
GoTo CallParent
Case "final"
colname = "Act. Yield"
GoTo CallParent
Case Else
GoTo CleanFail
End Select
Case "UNIT PRICE", "PRICE"
Select Case LCase(payment)
Case "initial"
colname = "Initial Payment"
GoTo CallParent
Case "final"
colname = "Act. Final Payment"
GoTo CallParent
Case Else
GoTo CleanFail
End Select
Case Else
GoTo CleanFail
End Select
CleanFail:
caskinfoBySKU_DYN = "•" 'ALT+0149
Exit Function
CallParent:
Call caskinfoBySKU(SKU, colname)
End Function
caskinfoBySKU_DYN = caskinfoBySKU(SKU, colname)而不是Call caskinfoBySKU(SKU, colname)起了作用。
感谢CDP1802