Access Database Design and Programming, Second Edition

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 12.1 in Chapter 12 (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 "mdb"
    FileType = "Database"
  Case "txt"
    FileType = "text"
  Case "dbf"
    FileType = "dBase"
  Case Else
    FileType = "unknown"
End Select

' Display result
MsgBox FileType

End Sub

Note that VBA allows us to place more than one condition in the same Case statement (separated by commas). This is useful when more than one case produces the same result.