The Case statement is a very common and useful function used in VBA. Using the case statement, you can run one of several groups of statements, depending on the value of an expression. It is an easier way to write multiple IF-THEN-ELSE statements.

In this article, we will see the syntax, uses and examples of the Case Statement. Let’s start with the syntax.

Select Case test_expression

  Case condition_1
    statements_Group1

  Case condition_2
    statements_Group2

...

  Case condition_n
    statements_Group_n

  [Case Else
    statements_default]

End Select


where
test_expression (required)
is any numeric expression or string expression. This is the expression that will be compared to the list of conditions (1 to n)
expressionlist-n (condition_1 to n)
Required if a Case appears. It is a delimited list of one or more expressions. It can be a single value, a range of values (using To, is and like keywords) or even a variable. We will see examples of these soon.
Once a match is found all the statements in the corresponding statement group are executed and the control goes to the next statement after End Select. Note that, if more than one matches are found, only statements corresponding to the first match will be executed.
statements-n (statements_Group1 to n)
The statements to be executed when a match for the expression is found. (optional)
elsestatements (case else and statements_default)
If the test_expression doesn’t match any of the Case clauses, the case “else” is executed. Though it is optional, it is a good practice to include it for handling of unforeseen test_expression values.
Let us have a look at some of examples of the conditions, before we look at a working example of the case statement.
Example 1: Some of the valid numerical expressions
Sub selectCaseNumeric()

Dim testNumber As Integer, maxNumber As Integer

Select Case testNumber

    Case 1 To 10
        'Select a range of values from 1 to 10, inclusive
        Debug.Print "Range"
    Case 11, 12, 15
        'Comma separated list
        Debug.Print "List"
    Case Is > 30
        'Comparison using is
        Debug.Print "Comparison"
    Case maxNumber
        'Equating to a variable
        Debug.Print "Variable"
    Case 1 To 10, 12, Is > maxNumber
        'Combining multiple expression types
        Debug.Print "Combination"
    Case Else
        'When none of the above conditions are true
        Debug.Print "Default"
    End Select

End Sub


Example 2: Dealing with Strings
Sub selectCaseString()

Dim testString As String, compString As String

Select Case testString

    Case "A" To "K"
        'strings that fall between "A" and "K" in alphabetic order (case sensitive)
        Debug.Print "Example: Apple, Banana"
    Case "a" To "k"
        Debug.Print "Example: apple, banana"
    Case "Orange", "Mango"
        'Match specified strings (case sensitive)
        Debug.Print "Orange or Mango only"
    Case compString
        'Equating to a variable
        Debug.Print "Variable"
    Case Is > "papaya"
        'Equivalent to between papaya and z
        Debug.Print "Using is"
    Case Else
        'When none of the above conditions are true
        Debug.Print "Default"
    End Select

End Sub


Example 3:
Here is a practical example of using the select case statement. Say that you have the marks obtained for a list of students in a spreadsheet, as shown below.

Now, for each student, you need to get the grade and color code the cell based on the grade obtained. Here is how you will use the select statement to achieve this.
Sub getGrades()
    Dim dataSheet As Worksheet
    Dim i As Integer

    Set dataSheet = Sheet1

    For i = 3 To 22

        Select Case dataSheet.Cells(i, 4).Value

            Case Is >= 0.9
                dataSheet.Cells(i, 5).Value = "Grade A"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 10

            Case Is >= 0.75
                dataSheet.Cells(i, 5).Value = "Grade B"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 4

            Case Is >= 0.6
                dataSheet.Cells(i, 5).Value = "Grade C"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 6

            Case Is >= 0.4
                dataSheet.Cells(i, 5).Value = "Grade D"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 46

            Case Is >= 0
                dataSheet.Cells(i, 5).Value = "Grade E"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 3

            Case Else
                dataSheet.Cells(i, 5).Value = "Error"
                dataSheet.Cells(i, 5).Interior.ColorIndex = 2

        End Select
    Next
End Sub


Note that, the condition that we have specified for the second case is “Is >= 0.75“. This will not include values >=0.9 because, if the value is >=0.9, the first case expression will be matched and the statements corresponding to the first case will be executed. The control will never reach the second case. Same explanation follows for the subsequent cases
If the marks entered are negative, the else case will be executed. If you want to take care of cases where the marks are greater than 100%, you can simply add a case “is > 1” as the first one.
After you run the code, the output will look like:

Before concluding, let us look at a very likely question that arises — when to use if-else and when to use select case statements.
From performance point of view, for just a few items, the difference in the execution time between these two statements is negligible. However, from readability point of view, the select case statements are easy to read and follow. They are also less prone to errors than the nested if-else conditions. So, the bottom line is that the select statements are more preferable over the nested if else statements in most of the cases.
If you have a large number of items, you should consider using the switch statement for performance improvement.