The Switch Function

The syntax of the Switch function is:

Switch(expr1, value1, expr2, value2, ... , exprn, valuen)

where exprn and valuen are expressions. Note that there need only be one expression-value pair, but the function is more meaningful if there are at least two such pairs.

The Switch function evaluates each expression exprn. When it encounters the first True expression, it returns the corresponding value. As with the IIf function, Switch always evaluates all of the expressions. If none of the expressions is True, the function returns Null. This can be tested with the IsNull function.

The procedure in Example 7-1 displays the type of file based on its extension: Template, Workbook, or Add-in.

Example 7-1. The Switch Function

Sub ShowFileType(FileExt As String) Dim FileType As Variant

FileType = Switch(FileExt = "xlt", "Template",

FileExt = "xls", "Workbook", FileExt = "xla", "Addin")

' Display result

If Not IsNull(FileType) Then

MsgBox FileType Else

MsgBox "Unrecognized type" End If End Sub

There is one subtlety in this code. Since the Switch function can return a Null value, we cannot assign the return value to a String variable, as we might first try to do:

Dim FileType As String

FileType = Switch(FileExt = "xlt", "Template",

FileExt = "xls", "Workbook",

This will not produce an error unless FileExt is not "xlt," "xls," or "xla," in which case we will get the very annoying error message, "Invalid use of Null." The solution is to declare FileType as a Variant, which can hold any data type, including no data type, which is indicated by the Null keyword. (This issue can also be avoided by using a Select Case statement, discussed in Chapter 8.)

0 0

Post a comment