Mahfuza Anika Era
Are you trying to exit a Select Case Statement for a specific condition in Excel VBA? It greatly helps to optimize the performance of the code. Unfortunately, VBA doesn’t have any Exit Select Case Statement. But I have an alternative solution to your problem. I have shown 3 real-life examples where it is necessary to exit a Select Case Statement. I have used Exit Sub just after the condition where I need to end the Statement. To know more please follow the article step by step.
The above image shows the calculated salary of employees based on their Job Title and Performance Score. Moreover, if the code finds any invalid Job Title, it will stop executing the case statement block and show a message box.
Download Practice Workbook
You can download the practice workbook here.
How to Launch VBA Editor in Excel
There are various methods by which you can open the VBA editor. The most commonly used method is Developer Tab >> Visual Basic and the simplest way to launch the VBA editor is to press ALT+F11.
Then, go to Insert>>Module to create a new module.
If you don’t have the Developer tab in your Excel workbook, follow the below steps:
- First, right-click anywhere on the Tab Then, click on Customize the Ribbon…
- Go to Customize Ribbon >> Developer. Next, select it and finally click OK.
Overview of Select Case Statement in Excel VBA
The Select Case statement is preferable to use when there are multiple conditions in a code. A common format for using Select Case Statement:
Syntax of Select Case Statement:
Select Case expression
Case condition_1
outcome_1
Case condition_2
outcome_2
Case condition_n
outcome_n
Case Else
outcome_else
' handle any other cases
End SelectArguments:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
|---|---|---|
| test_expression | Required | A string or numeric value to compare with a set of conditions (condition_1, condition_2,….., condition_n) |
| condition_1, condition_2,….., condition_n | Required | These are the conditions 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 | Optional | If none of the conditions match, then the statement under the else statement will execute. |
In conditions, you can use operators (=, >,<,<>,>=,<=), a range (Case 7 To 16 or Case “C” To “G”), commas to include multiple conditions in a specific case (Case 1, Is > 20, 10 To 15) or no operator at all.
Best Practices for Using Exit Select Statement
We can use “Exit Sub” to stop executing a Select Case. It immediately stops executing the “Select Case” block. Simple Select Case example with Exit Sub Statement.
Select Case x
Case 1
' do something
Case 2
' do something else
If y = 3 Then
Exit Sub
End If
Case 3
' do something different
Case Else
' handle any other cases
End SelectHere, when the code satisfies the condition x=2, it will run the related statement but if it satisfies y=3, then it will exit the Select Case block.
Exit Sub optimizes the code and helps to create neat and maintainable code. Some best practices for using exit select are mentioned below:
- To maintain the clean code, only use the Exit Sub for a reason. You can also consider using a comment to explain the reason for better understanding.
- Another best practice is to test out the code for the exit condition and other conditions to check if you are getting the desired output.
- Try to keep it simple.
Is Exit Select Case Statement Available in Excel VBA?
The answer is “No”, there is no “Exit Select Case” statement in Excel VBA. However, you can achieve the same functionality of ending the Exit Case early by using “Exit Sub” or “Exit Function” statements within the Select Case block. When the condition you’re looking for is met, you can use the “Exit Sub” or “Exit Function” statement to exit the entire procedure.
Excel VBA to Exit Select Case: 3 Cases
In this part, I am going to show the step-by-step process to use Exit Sub in the select case statement. 3 examples are provided based on real-life situations. So let’s get started with our first example.
Example 01: Exit Select Case If Job Title is Invalid with VBA
I want to calculate the salaries of Employees based on their Job Title and Performance Score. Managers with a performance score of 80 or higher receive a base salary of $5,000 plus a bonus of $1,000, Supervisors with a performance score of 70 or higher receive a base salary of $4,000 plus a bonus of $800, Staff with a performance score of 60 or higher receives a base salary of $3,000 plus a bonus of $500, and all other employees receive an error message. Here, my Excel version is Microsoft Excel 365.
To show the calculated salary information, I have added another column on the right side of the dataset. To write the VBA code,
In my problem, I want to stop calculating salary when a Job Title is found other than Manager, Supervisor, and Staff.
As I want to stop executing when the Job Title does not match, I will put the Exit Sub statement in the ELSE statement.
Implementing VBA code to solve this specific problem:
Sub CalculateSalary()
Dim jobTitle As String
Dim performanceScore As Variant
Dim salary As Double
For i = 5 To 14
jobTitle = Cells(i, 3).Value
performanceScore = Cells(i, 4).Value
Select Case jobTitle
Case "Manager"
If performanceScore >= 80 Then
salary = 5000 + 1000
Else
salary = 5000
End If
Case "Supervisor"
If performanceScore >= 70 Then
salary = 4000 + 800
Else
salary = 4000
End If
Case "Staff"
If performanceScore >= 60 Then
salary = 3000 + 500
Else
salary = 3000
End If
Case Else
MsgBox "Invalid Job Title Found"
Exit Sub
End Select
Cells(i, 5).Value = salary
Next i
End Sub🛠️ Code Breakdown
Sub CalculateSalary()- The code defines a subprocedure named “CalculateSalary”
Dim jobTitle As String
Dim performanceScore As Variant
Dim salary As Double- Three variables are declared to store job title, performance score, and salary.
For i = 5 To 14A loop is initiated, which will loop through rows 5 to 14 of the active worksheet.
jobTitle = Cells(i, 3).Value
performanceScore = Cells(i, 4).Value - The code retrieves the job title and performance score values for the current row.
Select Case jobTitle- The code uses a Select Case statement to determine the salary based on the job title and performance score.
Case "Manager"
If performanceScore >= 80 Then
salary = 5000 + 1000
Else
salary = 5000
End If- If the job title is “Manager”, I set the salary to $5,000, plus a $1,000 bonus if the performance score is 80 or above.
Case "Supervisor"
If performanceScore >= 70 Then
salary = 4000 + 800
Else
salary = 4000
End If- If the job title is “Supervisor”, the salary is set to $4,000, plus an $800 bonus if the performance score is 70 or above.
Case "Staff"
If performanceScore >= 60 Then
salary = 3000 + 500
Else
salary = 3000
End If- If the job title is “Staff”, the salary is set to $3,000, plus a $500 bonus if the performance score is 60 or above.
Case Else
MsgBox "Invalid Job Title Found"
Exit Sub
End Select - If the job title is not one of the above options, the code will display a message box indicating that an invalid job title was found, and the subprocedure exits.
Cells(i, 5).Value = salary- This line set the salary value for the current row as the fifth column of the active worksheet.
Next i
End Sub- The loop continues until all rows from 5 to 14 have been processed.
The output of the code will be like the image below. Thus, you can use the Exit Select Case statement to exit your VBA code in Excel.
Example 02: Exit Select Case When F Grade Is Found in Grading List
Suppose, I have a dataset where I have Student’s ID and Marks of some students. I want to grade the marks and I can use Select Case Statement to add multiple criteria for marks and grades. There may occur a situation where I want to stop executing the Select Case Statement if the code detects any failed marks. Following is the code which will stop the Select Case block If it finds an F grade in any cell. A MsgBox will also appear to notify the user.
Sub Determine_Grade()
Dim Marks As Range
Dim ID As Range
Dim Remarks As Range
Set MarksRange = Range("C5:C15")
For Each Marks In MarksRange
Set GradeCell = Marks.Offset(0, 1)
Select Case Marks.Value
Case 90 To 99
GradeCell.Value = "A"
Case 80 To 89
GradeCell.Value = "B"
Case 65 To 79
GradeCell.Value = "C"
Case 45 To 65
GradeCell.Value = "D"
Case Else
GradeCell.Value = "F"
MsgBox "Failed ID is found"
Exit Sub
End Select
Next Marks
End Sub🛠️ Code Breakdown
Sub Determine_Grade()- The code defines a VBA sub-procedure named “Determine_Grade”.
Dim Marks As Range
Dim ID As Range
Dim Remarks As Range- The procedure initializes three Range variables: Marks, ID, and Remarks.
Set MarksRange = Range("C5:C15")- This line sets the MarksRange variable to the range of cells C5 to C15.
For Each Marks In MarksRange- The code loops through each cell in the MarksRange using a For Each loop.
Set GradeCell = Marks.Offset(0, 1)- Then, this line sets the GradeCell variable to the cell one column to the right of the current Marks cell.
Select Case Marks.Value- The Select Case statement checks the value of the current Marks cell.
Case 90 To 99
GradeCell.Value = "A"
Case 80 To 89
GradeCell.Value = "B"
Case 65 To 79
GradeCell.Value = "C"
Case 45 To 65
GradeCell.Value = "D"- If the value is between 90 and 99, I set the GradeCell to “A”. If the value is between 80 and 89, I set the GradeCell to “B”. If the value is between 65 and 79, set the GradeCell to “C”. If the value is between 45 and 65, set the GradeCell to “D”.
Case Else
GradeCell.Value = "F"- If the value is not within any of the above ranges, set the GradeCell to “F”.
MsgBox "Failed ID is found"
Exit Sub
End Select
Next Marks
End SubA message box is displayed if a student’s marks result in an “F” grade. The procedure exits if an “F” grade is encountered.
To see the result, Run the code by using Shortcut Alt+F5. The following image shows the final output.
Example 03: Exit Select Case If Any Product Run Out of Stock
I have a dataset where Book name, author, and quantity are provided. I want to get a status based on the quantity of each book. But if any book has reached Zero quantity I want to immediately stop the Select Case Statement along with a MsgBox that tells “ Zero Quantity reached”.
You can use Exit Sub to do this. Follow the code given below.
Sub Booklist_status()
Dim Quantity As Range
Set Quantity_Range = Range("D5:D14")
For Each Quantity In Quantity_Range
Set Status = Quantity.Offset(0, 1)
Select Case Quantity.Value
Case Is >= 10
Status.Value = "Available"
Case 1 To 9
Status.Value = "Need to Order"
Case Else
Status.Value = "Not Available"
MsgBox "Zero Quantity reached"
Exit Sub
End Select
Next Quantity
End Sub🛠️ Code Breakdown
Sub Booklist_status()- The first line is a Subroutine declaration.
Dim Quantity As Range- The second line declares a variable called “Quantity” as a range object.
Set Quantity_Range = Range("D5:D14")- The third line sets the variable “Quantity_Range” as a range of cells from D5 to D14.
For Each Quantity In Quantity_Range- The “For Each” loop starts iterating through each cell in the “Quantity_Range” range, assigning the value of the cell to the “Quantity” variable in each iteration.
Set Status = Quantity.Offset(0, 1)- The fifth line declares a variable called “Status” as a range object, setting it as the cell next to the current “Quantity” cell (i.e., one column to the right).
Select Case Quantity.Value- The “Select Case” statement checks the value of the “Quantity” cell and executes the appropriate case based on the condition:
Case Is >= 10
Status.Value = "Available"
Case 1 To 9
Status.Value = "Need to Order"
Case Else
Status.Value = "Not Available"
MsgBox "Zero Quantity reached"
Exit Sub
End Select- If the “Quantity” value is greater than or equal to 10, I set the “Status” value to “Available”. If the “Quantity” value is between 1 and 9, I set the “Status” value to “Need to Order”. If the “Quantity” value is not within the above ranges, I set the “Status” value to “Not Available”, and the code will display a message box saying that the zero quantity has been reached.
Next Quantity
End Sub- The “Next” statement ends the “For Each” loop. The “End Sub” statement ends the subroutine.
After writing the code in the VBA code editor, Run the code to see the result. The output image is provided below.
Select Case vs. If Then Else Statement in Excel VBA
- If there are multiple conditions, Select Case performs better than Then Else Statement.
- You can easily edit Select Case compared to Then Else Statement.
- They will provide the same results but considering the simplicity, the CASE statement is more simple than the IF-THEN-ELSE statement.
Frequently Asked Questions (FAQs)
1. VBA Select Case Between Numbers
You can use the Select Case statement in VBA to check if a value falls within a range of numbers. Here’s an example of how to use Select Case to check if a number is between 1 and 10.
Sub CheckNumberInRange()
Dim myNumber As Integer
myNumber = 5
Select Case myNumber
Case 1 To 3
MsgBox "The number is between 1 and 3."
Case 4 To 6
MsgBox "The number is between 4 and 6."
Case 7 To 10
MsgBox "The number is between 7 and 10."
Case Else
MsgBox "The number is outside the range of 1 to 10."
End Select
End Sub2. How to Use Exit Sub in VBA?
In VBA, we used the Exit Sub Statement to immediately exit a sub-procedure before it has completed all of its statements. Following is an example of using Exit Sub.
Sub ExampleSub()
If some_Condition = True Then
Exit Sub ' Exit the Sub procedure immediately
End If
‘More code here….
End SubIn this example, if some_Condition is True, it will execute the Exit Sub statement. And the Sub procedure will end without running any code that follows it.
3. How Do I Switch Cases in VBA?
Switch Case is more widely known as Select Case Statement. Switch Case or Select Case is an alternative to the If- Else statement. But when you have 3 or more than 3 conditions in IF-Else Statement, it is convenient to use the Switch Case or Select Case statement. Here is the syntax of the Select Case Statement.
Sub SelectCase_example ()
Select Case expression
Case value1
' Code block to execute if expression = value1
Case value2
' Code block to execute if expression = value2
Case value3
' Code block to execute if expression = value3
Case Else
' Code block to execute if none of the above conditions are met
End Select
End SubKey Takeaways from This Article
- Using Exit Sub Statement inside a Select Case Statement
- Practical examples of using Select Case Statement to apply multiple conditions
Things to Remember
It is not mandatory but a great practice to execute necessary cleanup if applicable. It will free up allocated space during program execution.
Conclusion
This article provides a solid understanding of the Select Case Statement in VBA and how to exit it when necessary. The article will also offer best practices and tips to help readers write efficient and maintainable VBA code. Follow our ExcelDemy page for regular tips and tricks regarding Excel. You can suggest your thoughts in the comments below.
