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, and 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 an example in the coming section of this tutorial. I will also show you how to use 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.
Select [ Case ] expression_to_test [ Case expression_list1 [ statements / block of code to execute ] ] [ Case expression_list2 [ statements / block of code to execute ] ] [ Case Else [ else statements / block of code to execute] ] End Select
Where:
| Part | Description |
| expression_to_test | expression_to_test after the Select keyword is required. For example, DayNumber. |
| expression_list |
|
| Else | If none of the Cases matches the test expression, the optional Else part executes. |
A simple example of using Select Case VBA
Let me start with a simple example for understanding how the 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.
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 which matches the third case.
An example of using ‘To’ keyword to define boundaries
In that case, the first value must be less than or equal to the second value. See the following example:
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 the ‘To’ keyword is used. As the value of the variable mark is = 85, it matches 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 expresssion_List. However, it must be convertible to the expression_to_test data type.
In the 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:
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, 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 the 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:
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 that an ActiveX button is added to the Excel sheet. As you click the button, the code behind will check the value of the Excel sheet A1 cell.
If this is White, Cyan, or Dark Blue, the B1 column will be updated by Black value.
Similarly, if the 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 the ‘Is’ keyword in the Select Case statement.
See an example below:
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.
