VBA Functions: Switch

	VBA Functions: Switch

Introduction

As an alternative to an If...Then condition, the Visual Basic language provides a function named Switch. Its primary syntax is:

Public Function Switch( _
    ByVal ParamArray VarExpr() As Variant _
) As Variant

This function takes one required argument. To use it in an If...Then scenario, pass the argument as follows:

Switch(ConditionToCheck, Statement)

In the ConditionToCheck placeholder, pass a Boolean expression that can be evaluated to True or False. If that condition is true, the second argument would be executed.

When the Switch() function has been called, it produces a value of type Variant (such as a string) that you can use as you see fit. For example, you can store it in a variable. Here is an example:

Private Sub cmdFunction_Click()
    Dim Status As Integer, EmploymentStatus As String

    Status = 1
    EmploymentStatus = "Unknown"

    EmploymentStatus = Switch(Status = 1, "Full Time")

    MsgBox "Employment Status: " & EmploymentStatus
End Sub

This would produce:

Switch

In this example, we used a number as argument. You can also use another type of value, such as an enumeration. When using the Switch function, if you call it with a value that is not checked by the first argument, the function produces an error. To apply this function to an If...Then...Else scenario, you can call it using the following formula:

Switch(Condition1ToCheck, Statement1, Condition2ToCheck, Statement2)

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True)

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -- Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes. Although you can spend a great deal of time tuning a conditional expression such as one involving a Switch() function, it is still possible that none of the expressions evaluates to true. In this case, you can add a last expression as True and provide a subsequent statement to use. The syntax you would use is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True,
       True, What To Do With A False Expression)