The Select Case Statement

As we have seen, the If . . . Then . . . construct is used to perform different tasks based on different possibilities. An alternative construct that is often more readable is the Select Case statement, whose syntax is:

Select Case testexpression Case value1

' statements to execute Case value2

' statements to execute

Case Else ' statements to execute otherwise End Select

Note that the Case Else part is optional. To illustrate, the following code is the Select Case version of Example 7-1 in Chapter 7, (see the discussion of the Switch function) that displays the type of a file based on its extension. I think you will agree that this is a bit more readable than the previous version:

Sub ShowFileType(FileExt As String) Dim FileType As Variant

Select Case FileExt Case "xlt"

FileType = "Template" Case "xls"

FileType = "Worksheet" Case "xla", "utl"

FileType = "Addin" Case Else

FileType = "unknown" End Select

' Display result MsgBox FileType End Sub

Note the penultimate case statement: Case "xla", "utl"

VBA allows us to place more than one condition in a case statement, separated by commas. This is useful when more than one case produces the same result.

if testexpression = valuel if testexpression = value2

0 0

Post a comment