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
Was this article helpful?