The VBA Switch Function

Related Function:
VBA IIF

Description

The VBA Switch function evaluates a list of Boolean expressions and returns a value associated with the first true expression.

The syntax of the function is:

Switch( Expr-1, Val-1, [Expr-2, Val-2], [Expr-3, Val-3], ... )

Where the function arguments are:

Expr-1,
[Expr-2], ...
-One or more boolean expressions to be evaluated.
Val-1,
[Val-2], ...
-The values to be returned if the corresponding Expr-1, [Expr-2], etc. is the first True expression.

If none of the supplied expressions evaluate to True, the Switch function returns the value Null.


VBA Switch Function Examples

Example 1

' Return a surname corresponding to a supplied forename.
Dim fname As String
Dim sname
fname = "Lucy"
sname = Switch( fname = "Mary", "Jones", fname = "Joseph", "Johnson", fname = "Lucy", "Smith" )
' sname is now equal to "Smith".

In the above call to the Switch function, the third expression, fname="Lucy" is the first expression to evaluate to True. Therefore, the function returns the associated surname, "Smith".


Example 2

' Return a name, depending on an integer value.
Dim i As Integer
Dim fname
i = 12
fname = Switch( i < 10, "Mary", i < 20, "Joseph", i < 30, "Lucy" )
' fname is now equal to "Joseph".

In the above call to the Switch function, the second expression, i<20 is the first expression to evaluate to True. Therefore, the function returns the associated name, "Joseph".

Note that both the second expression i<20 and the third expression, i<30 evaluate to True. However, the Switch function only returns the value corresponding to the first True expression in the supplied list.