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

score 13 · Accepted Answer · answered Mar 17 '11 at 14:26

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.

You beat me to it. See here for more info: http://www.cpearson.com/excel/Enums.aspx — mwolfe02, Mar 17 '11 at 14:29
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. — Joel Goodwin, Mar 17 '11 at 14:31
Worth to remember that an Enum is a _long_ integer, as per Pearson's website: http://www.cpearson.com/excel/Enums.aspx. — Tiago Cardoso, Mar 17 '11 at 17:20
It is also important to note something mentioned in the [cpearson](http://www.cpearson.com/excel/Enums.aspx) 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. — Bilbottom, Jul 20 '19 at 15:16

Oneide · Answer 2 · 2011-03-17T16:58:43.450

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.

VBA Function argument list select

2 Answers2

Linked

Related