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 if testexpression = value1   
  Case value2   
    ' statements to execute if testexpression = value2
   . . .

   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.