Tanjima Hossain
If you are looking for some of the easiest ways to use the VBA CASE statement, then you are in the right place. The CASE statement is a type of logical function, which you can use instead of the IF-THEN-ELSE statement. So, let’s get into the main article to know about the introduction and the uses of the CASE statement.
Download Workbook
VBA Case Statement: Syntax & Arguments
⦿ Syntax
Select Case test_expression
Case condition_1
result_1
Case condition_2
result_2
Case condition_n
result_n
Case Else
result_else
End Select
⦿ Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| test_expression | Required | A string or numeric value that you want to compare with a set of conditions (condition_1, condition_2,…..,condition_n) |
| condition_1, condition_2,…..,condition_n | Required | Conditions that you want to evaluate. When a condition is true, then the corresponding code will be executed and no further conditions will be evaluated. |
| result_1, result_2,…..,result_n | Required | The code is executed once the condition becomes true. |
| Case Else
result_else |
Optional | When the given conditions are not true, then it will be executed. |
⦿ Return Value
The Case statement executes the corresponding codes when the conditions are met.
⦿ Version
The Case statement was introduced in the Excel 2000 version and available for all versions after that.
13 Examples of Using VBA Case Statement
In this article, we will try to demonstrate the uses of the CASE statement with some random examples along with some examples including the following two tables.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
1. Using VBA Case Statement for Some Random Strings
Here, we will test out the Case statement for some random strings Yes, No, Cancel, etc.
Step-01:
➤ Go to Developer Tab >> Visual Basic Option
Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option
After that, a Module will be created.
Step-02:
➤Write the following code
Sub case1()
Dim R As VbMsgBoxResult
R = MsgBox("Select Yes/No/Cancel", vbYesNoCancel)
Select Case R
Case vbYes
MsgBox "Yes"
Case vbNo
MsgBox "No"
Case vbCancel
MsgBox "Cancel"
End Select
End SubHere, we have declared R as VbMsgBoxResult, and in the first message box we can select Yes, No, or Cancel, and then depending on our selection, we will get the next message box. For this purpose, we have defined three cases.
➤ Press F5.
Then you will get the following message box and if you select Yes
You will get another message box that shows Yes.
For selecting No in the first message box,
We are getting another message box with No
Finally, for selecting Cancel in the first message box
You will get Cancel in the second message box.
Read More: How to Use VBA Randomize Function in Excel (5 Examples)
2. Using VBA Case Statement for an Exact Match of Numbers
In this section, we will use the Case statement for an exact match with some random numbers.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case2()
Dim x As Integer
x = CInt(InputBox("Give any Integer Value"))
Select Case x
Case 10
MsgBox "The number is 10"
Case 20, 30, 40
MsgBox "The number is between 20 to 40"
Case Else
MsgBox "The number is not in between 10 to 40"
End Select
End SubWe have declared x as Integer and CINT will convert your given data into an integer value.
For two conditions we have created two cases which are for the values 10, 20, 30, and 40, and for other values, Case Else will run its code.
➤ Press F5.
Then you will get the following message box and if you write the value 10 and press OK,
Then you will get another message box which says “The number is 10”.
For writing the value 30 and pressing OK in the first message box,
We are getting a second message box saying “The number is between 20 to 40”.
If you write the values other than 10, 20, 30, or 40 like 60 and press OK,
Then you will get another message box that says “The number is not in between 10 to 40”.
Read More: How to Use VBA IsNumeric Function (9 Examples)
3. Using VBA Case Statement for a Range of Numbers
Here, we will use a VBA code, which will give us a grade depending on the range of numbers.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case3()
Dim Score As Integer
Dim Grade As String
Score = InputBox("Enter the marks of a student")
Select Case Score
Case 90 To 100
Grade = "A"
Case 80 To 90
Grade = "B"
Case 70 To 80
Grade = "C"
Case 60 To 70
Grade = "D"
Case Else
Grade = "F"
End Select
MsgBox "The Student's Grade is: " & Grade
End SubHere, we have declared Score as Integer and Grade as String and for different ranges of marks, we have defined different cases which will return us different grades depending on our given input number. For declaring ranges we have used the TO operator like in Case 90 To 100.
➤ Press F5.
After that, you will have an input box where you have to enter the marks of a student (here, we have used 85) and press OK.
In return, it will give us another message box saying “The student’s Grade is: B”.
Read More: VBA If – Then – Else Statement in Excel (4 Examples)
4. Using VBA Case Statement with IS Keyword for Comparing Values
We will do the same task of the previous section here, unlike using the TO operator, we will use the IS keyword with the CASE statement.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case4()
Dim Score As Integer
Dim Grade As String
Score = InputBox("Enter the marks of a student")
Select Case Score
Case Is >= 90
Grade = "A"
Case Is >= 80
Grade = "B"
Case Is >= 70
Grade = "C"
Case Is >= 60
Grade = "D"
Case Else
Grade = "F"
End Select
MsgBox "The Student's Grade is: " & Grade
End SubHere, we have declared Score as Integer and Grade as String and for different ranges of marks, we have defined different cases which will return us different grades depending on our given input number. For declaring ranges we have used the IS keyword like in Case Is >= 90.
➤ Press F5.
Afterward, you will have an input box where you have to enter the marks of a student (here, we have used 50) and press OK.
As a result, it will give us another message box saying “The student’s Grade is: F”.
Read More: How to Use VBA Val Function in Excel (7 Examples)
5. Checking for an Exact Match of Texts
Here, we will test out the exact matching of texts with the Case statement.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case5()
Dim X As String
X = InputBox("Enter the name of a product")
Select Case X
Case "Tomato", "Broccoli", "Spinach"
MsgBox "Vegetable"
Case "Apple", "Banana", "Orange"
MsgBox "Fruit"
End Select
End SubHere, we have declared X as String and X will be assigned to a value which we will enter as an input. For vegetables we have created a case that will give a message box saying “Vegetable” and the other case is for fruits which will show “Fruit” in the message box.
➤ Press F5.
After that, we entered Orange (for entering orange or ORANGE or with any other case arrangements you will not get any output) in the input box and pressed OK.
As a result, we have a message box saying “Fruit”.
Related Content: How to Remove Duplicates in Excel Sheet (7 Methods)
6. Checking for Case Insensitive Texts
In the previous section, we had to enter the input carefully with a matching case for getting output, but if you don’t want to match the cases then the code in this section is for you.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Option Compare Text
Sub case6()
Dim X As String
X = InputBox("Enter the name of a product")
Select Case X
Case "Tomato", "Broccoli", "Spinach"
MsgBox "Vegetable"
Case "Apple", "Banana", "Orange"
MsgBox "Fruit"
End Select
End SubHere, we have used Option Compare Text so that you can enter your input in any type of case, and then we have declared X as String and X will be assigned to a value which we will enter as an input. For vegetables we have created a case that will give a message box saying “Vegetable” and the other case is for fruits which will show “Fruit” in the message box.
➤ Press F5.
After that, we entered broccoli in the input box and pressed OK.
As a result, we have a message box saying “Vegetable”.
Read More: VBA Format Function in Excel (8 Uses with Examples)
7. VBA Case Statement with Colon
In this section, we will use the Case statement with the Colon operator for defining different ranges of numbers.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case7()
Dim Score As Integer
Dim Grade As String
Score = InputBox("Enter the marks of a student")
Select Case Score
Case 90 To 100: Grade = "A"
Case 80 To 90: Grade = "B"
Case 70 To 80: Grade = "C"
Case 60 To 70: Grade = "D"
Case Else: Grade = "F"
End Select
MsgBox "The Student's Grade is: " & Grade
End SubHere, we have declared Score as Integer and Grade as String and for different ranges of marks, we have defined different cases which will return us different grades depending on our given input number. For declaring ranges we have used the “:” operator like in Case 90 To 100: Grade = “A” in which the part before “:” operator is the condition and the part after this operator will be executed when the condition will become true.
➤ Press F5.
Afterward, you will have an input box where you have to enter the marks of a student (here, we have used 96) and press OK.
As a result, it will give us another message box saying “The student’s Grade is: A”.
Read More: How to Use VBA UCASE Function in Excel (4 Examples)
Similar Readings:
- How to Use VBA IsEmpty Function (5 Relevant Examples)
- Use the VBA Weekday Function (2 Suitable Examples)
- How to Use VBA And Function in Excel (4 Examples)
- Use Log Function in Excel VBA (5 Suitable Examples)
- How to Use VBA Abs Function in Excel (9 Examples)
8. Checking If a Number Is Even or Odd
In this section, with a VBA code, we will check if our given number is even or odd.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case8()
Dim X As Integer
X = InputBox("Enter a number")
Select Case X Mod 2
Case 0
MsgBox "The number is even."
Case 1
MsgBox "The number is odd."
End Select
End SubWe have declared X as Integer and it will store the value which we are giving input. Here, the MOD operator will give the remainder of a division and when the value of this remainder is 0 the number will be even otherwise it will be odd.
➤ Press F5.
Afterward, you will have an input box where you have to enter a number (here, we have used 20) and press OK.
In return, we are getting a message box saying “The number is even”.
If you enter 33 and press OK
Then, you will get a message box that says “The number is odd”.
Read More: How to Use TRIM Function in VBA in Excel (Definition + VBA Code)
9. Using VBA Case Statement for Multiple Conditions
In this section, for multiple conditions, we will use the nested CASE statements.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case9()
Dim Gender As String
Dim Group As String
Gender = "Female"
Group = "Science"
Select Case Gender
Case "Male"
Select Case Group
Case "Science"
MsgBox "Male from Science"
Case "Commerce"
MsgBox "Male from Commerce"
End Select
Case "Female"
Select Case Group
Case "Science"
MsgBox "Female from Science"
Case "Commerce"
MsgBox "Female from Commerce"
End Select
End Select
End SubHere, we have declared Gender, Group as String and set Female for Gender and Science for Group. Within a CASE Gender, we have used another CASE Group and created nested CASE statements.
➤ Press F5.
Afterward, you will have a message box which says “Female from Science”.
Related Content: How to Use VBA Rnd in Excel (4 Methods)
10. Using VBA Case Statement with Cell Value
By using the CASE statement, we will define the grades of the marks of the students of the Marks column.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case10()
Dim cell As Range
For Each cell In Range("D5:D11")
Select Case cell.Value
Case 90 To 100
cell.Offset(0, 1) = "A"
Case 80 To 90
cell.Offset(0, 1) = "B"
Case 70 To 80
cell.Offset(0, 1) = "C"
Case 60 To 80
cell.Offset(0, 1) = "D"
End Select
Next cell
End SubWe have declared the cell as Range and used a FOR loop for the cells of the range “D5:D11” and for different ranges of marks we have defined different cases which will return us different grades depending on the numbers in the cells. For declaring ranges we have used the TO operator like in Case 90 To 100 and cell.Offset(0, 1) will return the output values in one column later to the input column.
➤ Press F5.
After that, we will have the grades for different students in the Grade column.
Read More: How to Return a Value in VBA Function (Both Array and Non-Array Values)
11. Creating Function Using VBA Case Statement
With the help of the CASE statement, we will define the grades of the marks of the students of the Marks column by creating a function.
Step-01:
➤ Follow Step-01 of Section 1.
➤ Write down and save the following code
Function GRADE(Score As Integer) As String
Select Case Score
Case 90 To 100
GRADE = "A"
Case 80 To 90
GRADE = "B"
Case 70 To 80
GRADE = "C"
Case 60 To 70
GRADE = "D"
Case Else
GRADE = "F"
End Select
End FunctionThis code will create a function named GRADE.
For different ranges of marks, we have defined different cases which will return us different grades depending on the numbers in the cells, and for declaring ranges we have used the TO operator like in Case 90 To 100.
Step-02:
➤ Return to the main sheet and type the following formula in the cell E5
=GRADE(D5)D5 is the Mark of a student and GRADE will return the corresponding grade of this mark.
➤ Press ENTER and drag down the Fill Handle tool.
As a result, we will have the grades for different students in the Grade column.
Read More: How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
12. Using VBA Case Statement for Dates
Here, we have some order dates and we want to know in which quarter of the year these dates fall into and to do this we will create a function with the help of a VBA code.
Step-01:
➤ Follow Step-01 of Section 1.
➤ Write down and save the following code
Function Quarter(dt As Date) As Integer
Dim sht As Worksheet
Select Case dt
Case CDate("01/01/2021") To CDate("03/31/2021")
Quarter = 1
Case CDate("04/01/2021") To CDate("06/30/2021")
Quarter = 2
Case CDate("07/01/2021") To CDate("09/30/2021")
Quarter = 3
Case CDate("10/01/2021") To CDate("12/31/2021")
Quarter = 4
End Select
End FunctionThis code will create a function named Quarter and we have used 4 CASE statements defining the ranges of dates for 4 parts of the year 2021.
Step-02:
➤ Return to the main sheet and type the following formula in the cell E5
=Quarter(C5)C5 is the Order Date and Quarter will return a value depending on which quarter of the year this date falls into.
➤ Press ENTER and drag down the Fill Handle tool.
In this way, you will get the quarter number of the Order Dates.
Read More: How to Use VBA FileDateTime Function in Excel (3 Uses)
13. Using VBA Case Statement for Checking Days
Using the VBA CASE statement here we will define the days of the corresponding Order Dates.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub case13()
Dim dt As Date
Dim cell As Range
For Each cell In Range("C5:C12")
dt = CDate(cell)
Select Case Weekday(dt)
Case vbMonday
cell.Offset(0, 2) = "Monday"
Case vbTuesday
cell.Offset(0, 2) = "Tuesday"
Case vbWednesday
cell.Offset(0, 2) = "Wednesday"
Case vbThursday
cell.Offset(0, 2) = "Thursday"
Case vbFriday
cell.Offset(0, 2) = "Friday"
Case vbSaturday
cell.Offset(0, 2) = "Saturday"
Case vbSunday
cell.Offset(0, 2) = "Sunday"
End Select
Next cell
End SubHere, we have declared dt, cell as Date, Range and used a FOR loop for the cells of the range “C5:C12”. For seven days of a week, we have put seven conditions with the CASE statements and cell.Offset(0, 2) will return the output values in two columns later to the input column.
➤ Press F5.
Afterward, we will have the Order Days for different Order Dates in the Order Days column.
Read More: How to Use IsDate Function in VBA (3 Examples)
Case Statement vs IF-THEN-ELSE Statement
- For testing multiple conditions, it is easy to write a code using the CASE statement rather than the IF-ELSE-THEN statement.
As we can see the following two codes
Sub casevsif()
Dim x As Integer
x = CInt(InputBox("Give any Integer Value"))
Select Case x
Case 10
MsgBox "The number is 10"
Case 20
MsgBox "The number is between 20"
Case Else
MsgBox "The number is not in between 10 to 20"
End Select
End Sub
Sub casevsif1()
Dim x As Integer
x = CInt(InputBox("Give any Integer Value"))
If x = 10 Then
MsgBox "The number is 10"
End If
If x = 20 Then
MsgBox "The number is 20"
Else
MsgBox "The number is not in between 10 to 20"
End If
End Sub
These two codes will give the same results but considering the simplicity, the CASE statement is more simple than the IF-THEN-ELSE statement.
- To make any changes we can find that the CASE statement is easier than the IF-THEN-ELSE statement.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, we tried to cover some of the ways to use the VBA CASE statement. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
