The Select Case is another decision making statement in VBA that may be useful for scenarios where you have a single expression to evaluate that has several possible values.
For example, you require executing a block of code based on the day of the Week. In that case, you may get the current day from the system, assign it to a variable. Use this variable as an expression in the Select VBA statement.
Now this expression can hold seven possible values – Monday to Saturday. Each day value can be tested in the Case statement with its block of statement to execute. I will show you such example in the coming section of this tutorial. I will also show you using Select case with Microsoft Excel worksheet, first the syntax of using Select statement.
Syntax of using Select case statement
Following is the general structure of using the Select case in VBA.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Select [ Case ] expression_to_test [ Case expressionlist1 [ statements / block of code to execute ] ] [ Case expressionlist2 [ statements / block of code to execute ] ] [ Case Else [ elsestatements / block of code to execute] ] End Select |
Where:
- expression_to_test after the Select keyword is required. For example, DayNumber.
- This is followed by the Case keyword and expressionlist. If expression in the Case matches the testexpression, the optional statements inside that case will execute. The expression can be used in a variety of ways like testing a single value or range of values etc. This will be clear in examples below.
- If none of the Case matches the testexpression the optional Else part executes.
A simple example of using Select Case VBA
Let me start with a simple example for understanding how Select Case statement works in VBA (before doing this in Excel). An Integer type variable is declared with an initial value. That variable is used in the Select Case and three cases are used to test its value.
A message box statement is executed for the matched case. In the end, the Else statement is also used.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Private Sub Sel_case_exmample() 'Example of using Select Case Dim intA As Integer intA = 30 Select Case intA Case 10 MsgBox "First Case is matched!" Case 20 MsgBox "The Second Case is matched!" Case 30 MsgBox "Third Case is matched in Select Case!" Case Else MsgBox "None of the Case is matched!" End Select End Sub |
You see, the value of the variable is 30 that matched the third case.
An example of using ‘To’ keyword to define boundaries
The “To” keyword can be used in Case statement for defining the boundaries of range to test for expressiontest. In that case, the first value must be less than or equal to the second value. See the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Private Sub Sel_case_exmample() 'Example of using Select Case with 'To' Keyword Dim marks As Integer marks = InputBox("Enter mark fro 1 to 100?") Select Case marks Case 1 To 32 MsgBox "Fail!" Case 33 To 59 MsgBox "F Grade" Case 60 To 70 MsgBox "D Grade" Case 71 To 80 MsgBox "C Grade" Case 81 To 90 MsgBox "B Grade" Case 91 To 95 MsgBox "A Grade" Case 96 To 100 MsgBox "A+ Grade" Case Else MsgBox "Out of range" End Select End Sub |
The result:
In the code, you can see how ‘To’ keyword is used. As variable marks value is = 85, so it matched the following case:
Case 81 To 90
MsgBox “B Grade”
Try entering different values in the input text field and see the output yourself.
Using String data type in Select Case
You may use any data type in the expresssionList. However, it must be convertible to the testExpression data type. In above two examples, I used Integer type and cases also used numbers.
In the following example, the current day name is taken from the system and assigned to a String variable. That string variable is used in the Select Case statement while subsequent cases are tested with the day names as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Private Sub Sel_case_exmample() 'Example of using Select Case with 'To' Keyword Dim sDayName As String sDayName = Format(Date, "dddd") Select Case sDayName Case "Saturday" MsgBox "Saturday, Its Weekend dude!" Case "Sunday" MsgBox "Yay, Its Sunday!" Case "Monday" MsgBox "mmm, Monday Blues!" Case "Tuesday" MsgBox "Its Tuesday, lets do some work!" Case "Wednesday" MsgBox "Wed, Ok keep on working!" Case "Thursday" MsgBox "Thursday, Well closing the work!" Case "Friday" MsgBox "We are so close, Its Friday!" End Select End Sub |
As there is no possibility of a day other than used in the Case statements so no Else block was used.
Example of Excel VBA Select with multiple values
For this example, two more things are done. One, the excel cells are used in the Select Case. The other is using multiple values to check that are separated by commas in the Case statement. So, it serves the kind of ‘Or’ operator purpose in If statement.
The Excel workbook’s column’s A1 value is assigned to a variable. This variable is used in the Select Case as testExpression. For the matched case, the B2 cell will be updated as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Private Sub select_case_Click() Dim color As String color = Range("A1").Value Select Case color Case "White", "Cyan", "Dark Blue" Range("B1").Value = "Black" Case "Green", "Black", "Brown" Range("B1").Value = "Yellow" Case "Blue", "Sky Blue" Range("B1").Value = "Maroon" Case Else Range("B1").Value = "Pink" End Select End Sub |
You can see, an ActiveX button is added to the excel sheet. As you click the button, the code behind it will check the value of excel sheet A1 cell. If this is White, Cyan or Dark Blue, the B1 column will be updated by Black value.
Similarly, if A1 value is changed to Green, Black or Brown, the B2 value will be set as Yellow. You can see, how multiple color names (Strings) are separated in the Case statements by using commas.
A Demo of using ‘Is’ operator in Select Case
You may also use ‘Is’ keyword in the Select Case statement. You may use ‘Is’ keyword with the comparison operator like =, >=, <= etc.
See an example below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Private Sub select_case_Click() Dim num As String num = Range("A1").Value Select Case num Case Is = 1, 3, 5, 7, 9, 11 Range("B1").Value = "ODD" Case Is = 2, 3, 6, 8, 10, 12 Range("B1").Value = "Even" End Select End Sub |
Enter the value from 1-12 in the A1 cell and press the button to see how it updates the B1 cell by Even or ODD text.