VBA 函数参数列表选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5340002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Function argument list select
提问by jason m
I am looking to do something like the following:
我正在做类似以下的事情：
Public Function myFunc(vArg1 as string, vArg2 as string, vArg3 as ["A","B","C"])
End Function
Where the user gets a drop down list for vArg3 when they call it. This would be similar to the following:
用户在调用 vArg3 时会得到一个下拉列表。这将类似于以下内容：
Public Sub Main()
Call StrComp("A", "B", vbTextCompare)
End Sub
Where vbTextCompare can be chosen from a pre-defined list or arguments for the function.
其中 vbTextCompare 可以从函数的预定义列表或参数中选择。
Thanks
谢谢
回答by Fink
This is what is known as an enumeration. Here is a quick example:
这就是所谓的枚举。这是一个快速示例：
Public Enum DayOfWeek
Monday = 1
Tuesday = 2
Wednesday = 3
Thursday = 4
Friday = 5
Saturday = 6
Sunday = 7
End Enum
Public Function GetDrinkSpecial(day As DayOfWeek) As String
Select Case day
Case DayOfWeek.Monday
GetDrinkSpecial = " Tap Domestics"
Case DayOfWeek.Tuesday
GetDrinkSpecial = "2 for 1 Rail Mixers"
Case DayOfWeek.Wednesday
GetDrinkSpecial = " You-Call-Its"
Case DayOfWeek.Thursday
GetDrinkSpecial = " Bush Bottles"
Case DayOfWeek.Friday
GetDrinkSpecial = " Greenies"
Case DayOfWeek.Saturday
GetDrinkSpecial = "No Specials, Doh!"
Case DayOfWeek.Sunday
GetDrinkSpecial = "No Specials, Doh!"
Case Else
GetDrinkSpecial = "No Specials, Doh!"
End Select
End Function
Public Sub TestIt()
MsgBox GetDrinkSpecial(Monday)
MsgBox GetDrinkSpecial(Tuesday)
MsgBox GetDrinkSpecial(Wednesday)
MsgBox GetDrinkSpecial(Thursday)
MsgBox GetDrinkSpecial(Friday)
MsgBox GetDrinkSpecial(Saturday)
MsgBox GetDrinkSpecial(Sunday)
End Sub
This will get the desired 'Drop Down' Effect you are looking for when calling the function within the VBA editor. However, if you were to call 'GetDrinkSpecial' from within an excel cell formula, you will not have access to the enumeration, and would need to specifically pass it the long value of the enumeration.
在 VBA 编辑器中调用该函数时，这将获得您正在寻找的所需“下拉”效果。但是，如果您要从 Excel 单元格公式中调用“GetDrinkSpecial”，您将无法访问枚举，并且需要专门将枚举的长值传递给它。
回答by Oneide
Not exactly the way you asked.
不完全是你问的那样。
First, you must define a "Public Enum" for the possible options in a module (it can be the same module, as long it is not a class module)
首先，您必须为模块中的可能选项定义一个“公共枚举”（它可以是同一个模块，只要它不是类模块）
Public Enum myFuncEnum
OPTION_A
OPTION_B
OPTION_C
End Enum
Then, in the Function definition you should have:
然后，在函数定义中你应该有：
Public Function myFunc(vArg1 as string, vArg2 as string, vArg3 as myFuncEnum)
End Function
Remember that you must compare vArg3 with OPTION_A, OPTION_B and OPTION_C, instead of "A", "B" and "C". By the way, OPTION_A, OPTION_B, etc, may be renamed to make more sense, but they should be unique troughout the project.
请记住，您必须将 vArg3 与 OPTION_A、OPTION_B 和 OPTION_C 进行比较，而不是“A”、“B”和“C”。顺便说一句，OPTION_A、OPTION_B 等可以重命名以使其更有意义，但它们在整个项目中应该是唯一的。
