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)
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 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
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"
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