VBA Select Case – An Awesome Alternative to IF-THEN Statements
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
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
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.