VBA Function argument list select

Question

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:

            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.

Thanks

Fink · Accepted Answer · 2011-03-17 14:26:51Z

13

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 = "$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 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.

answered Mar 17, 2011 at 14:26

Fink

3,44622 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mwolfe02 Over a year ago

You beat me to it. See here for more info: cpearson.com/excel/Enums.aspx

Joel Goodwin Over a year ago

I should also point out that an "enum" is just a label for an integer. It is still possible to pass through "illegal values" like 57 through an enumerated function parameter, hence why Fink has the Case Else included here.

Tiago Cardoso Over a year ago

Worth to remember that an Enum is a long integer, as per Pearson's website: cpearson.com/excel/Enums.aspx.

Bilbottom Over a year ago

It is also important to note something mentioned in the cpearson website: "... Enums cannot be declared within a procedure. They must be declared within the declarations part of a module, above and outside any procedure in the module." Trying to use an Enum after defining other (independent) functions generated an error for me, which took me a while to figure out how to overcome.

Oneide · Accepted Answer · 2011-03-17 16:58:43Z

2

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.

edited Mar 17, 2011 at 16:58

answered Mar 17, 2011 at 14:28

Oneide

1,0308 silver badges12 bronze badges

Collectives™ on Stack Overflow

VBA Function argument list select

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related