Using Select Case in VBA

This page shows how you can use Select Case to determine which code block (after each Case statement) needs to be executed. The different ways to specify the Case clauses are shown. In the example below the string after Debug.Print explains what the Case's condition specifies. It is important to understand that (only) the block is executed that has matching condition. Select Case statements can be nested or mixed with If statements to implement decision trees. In the example below the value 9 matches both Case 8 To 10 (Between 8 and 10) and Case Is < 10 (Smaller than 10), the first match is executed.

Sub SelectDemo1()
    Dim Number: Number = 9    ' Demo value.
    Select Case Number    'Determine which branch is appropriate.
        Case 2 To 4
        Debug.Print "Between 2 and 4"
        
        Case 1, 7
        Debug.Print "1 or 7"
        
        Case 8 To 10
        Debug.Print "Between 8 and 10"
        
        Case Is < 10
        Debug.Print "Smaller than 10"
        
        Case Is > 12
        Debug.Print "Greater than 12"
         
        Case Else
        Debug.Print "Not any of the previous cases"
    End Select
End Sub

Select Case

After Select Case you can put any numeric expression or string expression, but in most cases one will use a variable here, as in SelectDemo1 Number. Below gives an example of using a string expression:

Dim Products As Collection, prod As Product
Dim i As Integer: i = 2
Set prod = Products(i)
Select Case prod.Category
Case "Fruit"

Select Case LCase

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"

Select Case Else

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"

Specifying Case

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

Case , (Or)

1 or 7

Case 1, 7

Case .. To ..

8 To 10: 8, 9, 10

Case 8 To 10

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

Case Is <

Case Is < 10 - any number below 10

Case Is < 10

Case Is >

Case Is > 10

Case Else