excel - Excel VBA函数B不显示调用函数A的结果

Question

我正在改进我的 Excel 表格以跟踪我分享的威士忌酒桶，但我遇到了一个奇怪的问题，至少对我来说这很奇怪。

我创建的caskinfoBySKU函数有效，基本上是一个 INDEX MATCH(row) MATCH(column) 函数。

第二个函数caskinfoBySKU_DYN也可以工作，但无法显示它应该从 caskinfoBySKU 函数获得的结果。caskinfoBySKU_DYN 为某些取决于付款类型（初始与最终）的列排序需要与 caskinfoBySKU 函数一起使用的列名称。

我不是经常在 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

标签： excelvbacall

Answer 1

caskinfoBySKU_DYN = caskinfoBySKU(SKU, colname)而不是 Call caskinfoBySKU(SKU, colname)成功了。

感谢 CDP1802