Geek Answers Handbook

VBA function argument selection

I want to do something like the following:

Public Function myFunc(vArg1 as string, vArg2 as string, vArg3 as ["A","B","C"]) End Function

When the user receives a drop-down list for vArg3, when he calls it. It will look like the following:

  Public Sub Main() Call StrComp("A", "B", vbTextCompare) End Sub

Where vbTextCompare can be selected from a predefined list or arguments for a function.

thanks

+6
vba excel-vba excel-2007
source share
2 answers

This is what is called 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 = "$1 Tap Domestics" Case DayOfWeek.Tuesday GetDrinkSpecial = "2 for 1 Rail Mixers" Case DayOfWeek.Wednesday GetDrinkSpecial = "$2 You-Call-Its" Case DayOfWeek.Thursday GetDrinkSpecial = "$1 Bush Bottles" Case DayOfWeek.Friday GetDrinkSpecial = "$3 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 give the desired “Drop Down” effect you are looking for when calling a function in the VBA editor. However, if you were to call "GetDrinkSpecial" from an excel cell formula, you would not have access to the enumeration, and you would need to specifically pass it a long enumeration value.

+11
source share

Not quite as you requested.

First you must define a “Public Enum” for the possible parameters in the module (it may be the same module if it is not a class module)

 Public Enum myFuncEnum OPTION_A OPTION_B OPTION_C End Enum

Then in the function definition you should:

 Public Function myFunc(vArg1 as string, vArg2 as string, vArg3 as myFuncEnum) End Function

Remember that you should compare vArg3 with OPTION_A, OPTION_B and OPTION_C instead of “A”, “B” and “C”. By the way, OPTION_A, OPTION_B, etc. You can rename it to make more sense, but they must be unique for the entire project.

+2
source share

More articles:


All Articles