19.4 Select Case Statements

A Select Case statement allows you to execute one of several groups of statements depending on the value of an expression. You could execute the same logic with a series of IF statements or If...Then, Else statements, but using a Select Case tends to be cleaner. Often, we will have VBA look at the value of a variable, and if it matches specific values, then we will execute one or more lines of code.

The basic structure of a case statement follows this pattern:

Select Case [variable or expression]
     Case [value1]
          [Instructions]
     Case [value2]
          [Instructions]
     Case [value3]
          [Instructions]
     Case Else 
          [Instructions]
End Select

The Select Case statement provides a variable or an expression that will be evaluated. Then there are separate case keywords with a matching value or expression. If the value in the first variable or expression matches that of a specific case, then the instructions immediately after that case will be executed. If the value doesn’t match, it tries the next case. If the variable or expression doesn’t match any of the values and there is a Case Else, then the instructions associated with that option will be executed. Like the IF statement, we include an End Select command to denote the end of a given Select Case block.

Consider an example where your function accepts a three-letter airport code and should return the full name of the airport. A Select Case statement can be used to concisely organize the codes and associated airport names.

'Select Case
Function Airport(code)
     Select Case UCase(code)
          Case "BKK"
               Airport = "Bangkok Suvarnabhumi International Airport"
          Case "CNX"
               Airport = "Chiang Mai International Airport"
          Case "ICN"
               Airport = "Incheon International Airport"
          Case "KUL"
               Airport = "Kuala Lumpur International Airport"
          Case "HKT"
               Airport = "Phuket International Airport"
          Case "REP"
               Airport = "Siem Reap International Airport"
          Case "SIN"
               Airport = "Singapore Changi Airport"
          Case Else
               Airport = "Unknown"
     End Select
End Function

A code will be passed into the function. Notice how the Select Case will evaluate the value of the code variable. However, because users might pass in a lower-cased value and since VBA text comparisons are case-sensitive, we will use the UCASE function to make sure that we are always comparing against the upper-cased version of the code. VBA will first check to see if the code equals BKK. If so, then it will return the full name of that Thai airport. If it doesn’t match, then VBA checks to see if it matches CNX. The process repeats itself until it finds a match or hits the Case Else statement.

Can you see how much more concise this approach is than using a series of If...Then, Else statements?

While we won’t get into it here, the case expressions can also include operators and ranges of values. As you get more experience with Select Case statements, you are sure to incorporate them in your VBA programs.