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.
Get Access Database Design and Programming, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.