How to Use VBA Case Statement (13 Examples)

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

VBA CASE

⦿ 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.

VBA CASE

VBA CASE

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

VBA CASE

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

random strings

After that, a Module will be created.

random strings

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 Sub

Here, 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.

random strings

➤ Press F5.

Then you will get the following message box and if you select Yes

random strings

You will get another message box that shows Yes.

random strings

For selecting No in the first message box,

random strings

We are getting another message box with No

random strings

Finally, for selecting Cancel in the first message box

random strings

You will get Cancel in the second message box.

VBA CASE

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 Sub

We 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.

exact match of numbers

➤ Press F5.

Then you will get the following message box and if you write the value 10 and press OK,

exact match of numbers

Then you will get another message box which says “The number is 10”.

VBA CASE

For writing the value 30 and pressing OK in the first message box,

exact match of numbers

We are getting a second message box saying “The number is between 20 to 40”.

exact match of numbers

If you write the values other than 10, 20, 30, or 40 like 60 and press OK,

exact match of numbers

Then you will get another message box that says “The number is not in between 10 to 40”.

VBA CASE

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 Sub

Here, 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.

VBA CASE

➤ 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.

range of numbers

In return, it will give us another message box saying “The student’s Grade is: B”.

range of numbers

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 Sub

Here, 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.

IS keyword

➤ 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.

IS keyword

As a result, it will give us another message box saying “The student’s Grade is: F”.

IS keyword

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 Sub

Here, 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.

VBA CASE

➤ 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.

exact match of texts

As a result, we have a message box saying “Fruit”.

exact match of texts

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 Sub

Here, 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.

VBA CASE

➤ Press F5.

After that, we entered broccoli in the input box and pressed OK.

case insensitive texts

As a result, we have a message box saying “Vegetable”.

case insensitive texts

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 Sub

Here, 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.

colon operator

➤ 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.

colon operator

As a result, it will give us another message box saying “The student’s Grade is: A”.

VBA CASE

Read More: How to Use VBA UCASE Function in Excel (4 Examples)


Similar Readings:


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 Sub

We 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.

even or 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.

even or odd

In return, we are getting a message box saying “The number is even”.

even or odd

If you enter 33 and press OK

even or odd

Then, you will get a message box that says “The number is odd”.

VBA CASE

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 Sub

Here, 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.

multiple conditions

➤ Press F5.

Afterward, you will have a message box which says “Female from Science”.

multiple conditions

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.

VBA CASE

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 Sub

We 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.

having grades with VBA Code

➤ Press F5.

After that, we will have the grades for different students in the Grade column.

VBA CASE

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.

VBA CASE

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 Function

This 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.

creating function

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.

creating function

➤ Press ENTER and drag down the Fill Handle tool.

creating function

As a result, we will have the grades for different students in the Grade column.

VBA CASE

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.

VBA CASE

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 Function

This 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.

dates

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.

dates

➤ Press ENTER and drag down the Fill Handle tool.

dates

In this way, you will get the quarter number of the Order Dates.

VBA CASE

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.

VBA CASE

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 Sub

Here, 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.

checking days

➤ Press F5.

Afterward, we will have the Order Days for different Order Dates in the Order Days column.

VBA CASE

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

VBA CASE

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

VBA CASE

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.

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo