excel - 使 VBA 函数参数成为供用户选择的列表

Question

我制作了一个示例函数，它根据国家和重量计算运费，我希望国家显示为下拉列表。就像使用函数时看到的一样Subtotal，所以用户在单元格中输入公式时可以选择。

我已经提到了这个问题和答案：VBA Function argument list select

该函数仅在作为第一个参数输入的枚举参考数时才起作用，并且它不采用单元格值。

请问：

1. 枚举描述值是否可以像函数一样显示为下拉列表Subtotal？

2. 第一个参数是否可以根据分配的用户从单元格中获取值？

这是示例代码：

Option Explicit
Public Enum nations
    USA = 1
    AU = 2
    CN = 3
    SG = 4
End Enum


Function intlCartage(country As nations, weight As Double)

Select Case country
    Case nations.AU
        intlCartage = 15 + WorksheetFunction.RoundUp((weight - 1), 0) * 10
    Case nations.CN
        intlCartage = 20 + WorksheetFunction.RoundUp((weight - 1), 0) * 5
    Case nations.SG
        intlCartage = 15 + WorksheetFunction.RoundUp((weight - 1), 0) * 10
    Case nations.USA
        intlCartage = 10 + WorksheetFunction.RoundUp((weight - 1), 0) * 8
    Case Else
        intlCartage = "please contact sales for quote."
End Select

End Function

标签： excelvba

Answer 1

枚举描述值是否可以像 Subtotal 函数一样显示为下拉列表？

我不相信，至少根据我的经验。这确实与您的下一个问题有关。

第一个参数是否可以根据分配的用户从单元格中获取一个值？

使用数据>数据工具>数据验证>数据验证（键盘快捷键 = Alt,D,L）并将值USA,AU,CN,SG作为可用选项列表。然后，您需要一个字符串到枚举转换器来为您进行自动转换。

我刚刚测试并使用两者得出了相同的值。我给你的原件打了电话，intlCartage以帮助表明它正在传递一个枚举。

'Standard Module
Public Function updatedCartage(ByVal country As String, ByVal weight As Double) As Variant
    Dim enumCountry As nations
    Dim nationConverter As NationsConverter
    Set nationConverter = New NationsConverter
    enumCountry = nationConverter.ToEnum(country)

    updatedCartage = intlCartage(enumCountry, weight)
End Function

您将把这段代码放在一个类模块中。我已将我的重命名为NationsConverteras Class1is not descriptive，完全没有。

'For Early binding set a reference to
'Tools>References> "Microsoft Scripting Runtime"
'then use Scripting.Dictionary instead of Object.
'You'd then change where you set the variable to
'New Scripting.Dictionary from CreateObject()
Private StringForEnum As Object
Private EnumForString As Object

Private Sub Class_Initialize()
    PopulateDictionaries
End Sub

Private Sub PopulateDictionaries()
    Set EnumForString = CreateObject("Scripting.Dictionary")
    EnumForString.Add "USA", nations.USA
    EnumForString.Add "AU", nations.AU
    EnumForString.Add "CN", nations.CN
    EnumForString.Add "SG", nations.SG

    Set StringForEnum = CreateObject("Scripting.Dictionary")
    Dim element As Variant
    For Each element In EnumForString.Keys
        StringForEnum.Add EnumForString.Item(element), element
    Next
End Sub

Public Function ToEnum(ByVal value As String) As nations
    value = UCase$(value)

    If Not EnumForString.Exists(value) Then
        ThrowInvalidArgument "ToEnum", value
    End If

    ToEnum = EnumForString(value)
End Function

Public Function ToString(ByVal value As nations)
    If Not StringForEnum.Exists(value) Then
        ThrowInvalidArgument "ToString", CStr(value)
    End If

    ToString = StringForEnum(value)
End Function

Private Sub ThrowInvalidArgument(ByVal source As String, ByVal value As String)
    Err.Raise 5, Information.TypeName(Me) & "." & source, "Invalid input '" & value & "' was supplied."
End Sub

Public Property Get Enums() As Variant
    Enums = EnumForString.Items
End Property

Public Property Get Strings() As Variant
    Strings = EnumForString.Keys
End Property