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

Answer 1

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.

Answer 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.