VBA自定义函数来比较值

Question

我正在尝试创建一个基本上执行此操作的自定义函数:

该函数位于 F 列中，并查看 C 列以查看其中的值。

如果是USA_USD它将显示 USD F列

如果是Australia_AUD，它将显示AUD F列

等

Function The_Decider(DealCurrency, X)       
    If DealCurrency = "USA_USD" Then X = "USD"
    Else
    If DealCurrency = "Australia_AUD" Then X = "AUD"
    Else
    If DealCurrency = "Switzerland_CHF" Then X = "CHF"
    Else
    If DealCurrency = "China_CNY" Then X = "CNY"
    Else
    If DealCurrency = "EMU_EUR" Then X = "EUR"
    Else
    If DealCurrency = "Great Britain_GBP" Then X = "GBP"
    Else
    If DealCurrency = "Hong Kong_HKD" Then X = "HKD"
    Else
    If DealCurrency = "Japan_JPY" Then X = "JPY"
    Else
    If DealCurrency = "Korea(South)_KRW" Then X = "KRW"
    Else
    If DealCurrency = "Viet Nam_VND" Then X = "VND"
End Function

为什么我会收到 #value在 C 列？

Answer 1

首先，您实际上并没有在任何时候提供返回值。此外，您可能会更好地选择 Select Case为了可读性而阻塞。

您应该在函数头中声明返回类型，然后在代码中使用与函数相同的名称来返回值:

Function The_Decider(ByVal DealCurrency As String) As String '// <~~ return type

    Dim X As String

    Select Case DealCurrency
        Case "USA_USD":             X = "USD"
        Case "Australia_AUD":       X = "AUD"
        Case "Switzerland_CHF":     X = "CHF"
        Case "China_CNY":           X = "CNY"
        Case "EMU_EUR":             X = "EUR"
        Case "Great Britain_GBP":   X = "GBP"
        Case "Hong Kong_HKD":       X = "HKD"
        Case "Japan_JPY":           X = "JPY"
        Case "Korea(South)_KRW":    X = "KRW"
        Case "Viet Nam_VND":        X = "VND"
        Case Else:                  X = "Unknown"
    End Select

    The_Decider = X '// <~~ the part that actually returns the value

End Function

尽管看到您的输入似乎在最后 3 个字母中有货币代码，但我什至不会使用 UDF。只需使用工作表公式就足够了:

=RIGHT(C1,3)