VBA: Restrict function arguments to only selected values?

Question

Is it possible to define a VBA function so it will only accept specific predefined values as parameter values? As example, if I create a function like this:

Function Test(vTxt as Boolean)
    Msgbox vText
End Function

and call it from any other Sub I have to give True or False for vTxt, and it won't accept any other values. For example, I can call Test as:

Sub Example ()
    Call Test(True)
End Sub

Like that, can I set up other parameters in the function so that the caller must choose from only predetermined options? For example:

Function Test2(vTxt as <something>)
    Msgbox vText
End Function

and when calling Test2, the caller must specify Option 1, Option 2 or Option 3 for variable vTxt. I don't want to create multiple optional arguments, but just one argument with multiple predetermined values. What can I use for <something>?

How many different options do you need? You might look into using a Select Case method — TenderShortGoldenRetriever
your question is a bit confusing, what do you mean by: "And call it from any other Sub I have the option to give True or False for vTxt and it won't accept any other values. Like" because you give an example of calling it with a Boolean value. — Sorceri

cxw cxw · Accepted Answer · 2016-07-07T17:00:25

I think you want to give the user a limited number of choices for an argument. Enum types are a way to do that. At the beginning of a module, create your type:

Public Enum TestChoices
    Option1
    Option2
    Option3
End Enum

Then, in your function, take a TestChoices parameter:

Sub Test(vMsg as TestChoices)
    Dim message as String
    Select Case vMsg
        Case Option1: message="option 1 selected"
        Case Option2: message="option 2 selected"
        Case Option3: message="option 3 selected"
    End Select
    MsgBox message
End Sub

VBA: Restrict function arguments to only selected values?

1 Answers