Checking conditions

Using Select Case statements is an alternative to using If statements for specifying what code statements to execute depending on conditions in a procedure.

When to use: If or Select Case

If statements are essentially used to implement binary decisions trees the condition returns True or False determining what code the execute next. Select Case is used to branch on different sets of values. Adding ElseIf to the If block can be used to get the same expressiveness as Select Case, but it is less clear and may result in messy code mixing different arguments.


Dim Bonus As Currency, Salary As Currency Performance As Integer
'If as binary decision - either or 
If Performance < 5 Then    
    Bonus = Salary * 0.1 
ElseIf 
    Bonus = Salary * 0.09
End If
'ElseIf is the Visual Basic extension of If, but it is much more verbose than Select Case - below
If Performance = 1 Then    
    Bonus = Salary * 0.1 
ElseIf Performance = 2 Or Performance = 3 Then
    Bonus = 500
'...
End If
'Using Select Case is most clear - the variable being evaluated is only mentioned once at the top
Select Case Performance 
Case 1 
  Bonus = Salary * 0.1 
Case 2, 3 
  Bonus = Salary * 0.09 
Case 4 To 6 
  Bonus = Salary * 0.07 
Case Is > 8 
  Bonus = 100 
Case Else 
  Bonus = 0 
End Select

Different Select Case statement types

Select Case evaluates a value, usually a variable which has been assigned a value (here: Performance). Only one Case branch will be entered after which the code execution continues after the End Select The above example has different expressions on each Case statement line illustrating its expressiveness:

String comparison is case sensitive

The problem with Strings is that if just one character is uppercase in the Select Case expression and lowercase in the Case expression it will not been considered as a match. A way to prevent this is to make all characters lower- or upper-case:


Select Case LCase(prod.Category)
Case "fruit"

Combining expressions in one Case statement

In the Case statement one or more expressions may be combined, separated by a comma, meaning if .. or .. or .., in all those cases do the statement block from this Case.


Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

Interestingly, To can also be used with strings. In the below example value 'popcorn' would be considered a match for 'nuts To soup'


Case "everything", "nuts" To "soup", TestItem

Should Case Else be avoided?

More often then not Select Case Else is included as an easy way to make sure all possible cases are getting handled. However it may be argued that all known cases must be explicit and this one should be used for error handling.


Err.Raise Number:=cintUnspecifiedCaseInSelect, Description:="Unspecified Case in Select"
Select Case Builder in action

Select Case Builder

The Code VBA VBA editor add-in includes a code tool which speeds up writing Select Case code blocks: the Select Case Builder. Features: