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.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page