Overview of the Select Case Statement in Excel VBA
The Select Case structure allows you to test a variable or expression against multiple possible values and execute different code blocks based on the value. Here’s how it works:
- In VBA, the Select Case statement is used for this purpose.
- It evaluates an expression and then compares it to various cases.
- Depending on the match, specific code blocks are executed.
- The Case Else statement handles situations where the expression doesn’t match any previous cases.
Why Use the Select Case Statement?
- When dealing with multiple conditions against a single variable or expression, Select Case is preferable over If…Then.
- It enhances code readability and makes maintenance easier.
- Additionally, it’s more efficient than using multiple If…Then statements because it evaluates the expression only once.
Example 1 – Select Case Between Two Numeric Values
- You can use Select Case to compare exact values or cases with inequalities.
- Let’s start with comparing exact values:
1.1 Comparing with Exact Values
Sub select_exact()
Dim Agree As Integer
agree = InputBox("Enter 1 if agree,2 if not:")
Select Case agree
Case 1
MsgBox "Yes! Proceed please"
Case 2
MsgBox "Sorry! Access Denied"
End Select
End SubWhen the user inputs 1, it displays Yes! Proceed please. For 2, it shows Sorry! Access Denied.
1.2 Comparing Cases with Inequalities
To use inequalities, use the Case Is syntax:
Sub Compare_ineqality()
Dim num As Integer
num = InputBox("Enter your number:")
Select Case num
Case Is >= 80
MsgBox "You got A in the exam"
Case Is < 80
MsgBox "You missed the desired grade"
End Select
End SubIf the input value is >= 80, it displays You got A in the exam. If the input value is < 80, let’s use 55, it displays You missed the desired grade.
Read More: How to Use VBA Case Statement
Example 2 – Select Case Between Two Ranges
- You can use the Select Case statement with ranges in Excel VBA.
- In the following code, we check whether a patient’s temperature falls within a normal range or a feverish range:
Sub select_two_range()
Dim rng As Range
Set rng = Application.InputBox("Select the patient temperature:", Type:=8)
Select Case rng
Case 95 To 99.5
MsgBox "Normal Temperature"
Case 99.5 To 105.8
MsgBox "Fever Condition"
End Select
End Sub
The user selects a range of cells containing patient temperatures, and the code determines whether it’s normal or feverish.
Example 3 – Select Case Between Two String Values
- You can also use Select Case with string data.
Sub Select_string()
Dim str1 As String
Dim str2 As String
str1 = Application.InputBox("Give a string value:", Type:=2)
str2 = Application.InputBox("Give another string value:", Type:=2)
Select Case True
Case str1 < str2
MsgBox str1 & " comes before " & str2
Case str1 > str2
MsgBox str1 & " comes after " & str2
Case Else
MsgBox str1 & " and " & str2 & " are the same"
End Select
End Sub
The user inputs two strings (e.g., NewYork and Jamaica), and the code compares them to determine their order.
Example 4 – Select Case Between Two Values with Colon Operator
- You can use the Colon operator in combination with a range in the Select Case statement.
- In the following example, we check whether the speed of a car falls within a certain range and provide a corresponding message to the driver using a MsgBox:
Sub Select_with_colon()
Dim speed As Double
speed = InputBox("Enter the speed of your car:")
Select Case speed
Case 0 To 40:
MsgBox "Slow or Moderate speed"
Case 40 To 80:
MsgBox "Normal or high speed"
End Select
End SubThe code executes the appropriate block if the value being tested falls between 0 and 40 or between 40 and 80.
Example 5 – Select Case Between Two Values in a Custom Function
- Writing code to perform a specific task and then assigning it to a function is a useful way to check whether a value falls within a certain range.
- In this example, we create a custom function named Grade in Excel VBA. The function determines whether a given number corresponds to a Fail or a Pass based on predefined ranges:
The code defining the function is as follows.
Function Grade(number As Integer)
Dim comment As StringSelect Case number
Case 0 To 32
comment = "Fail"
Case 33 To 100
comment = "Pass"
End Select
Grade = comment
End Function
The function evaluates the input number and returns the corresponding grade (Fail or Pass.)
Read More: How to Use Excel VBA Select Case and Operator
Example 6 – Compare Two Values Using Case Is
- In a standard Excel VBA Select Case structure, inequalities cannot be directly used. However, we can employ the Case Is syntax to handle inequalities.
- In the following example, we check whether the temperature of a specific place is above or below room temperature:
Sub Case_Is()
Dim num As Range
Set num = Selection
var1 = num.Offset(0, 1).Value
Select Case var1
Case Is <= 25
num.Offset(0, 2).Value = "Below Room Temperature"
Case Is > 25
num.Offset(0, 2).Value = "Above Room Temperature"End Select
End Sub
The code compares the value of var1 to determine whether it’s below or above 25 and updates a cell accordingly.
Using Select Case with Multiple Variables – Numeric Values
We can apply Select Case to multiple variables in Excel VBA.
1. Multiple Variables Numeric Values with Select Case
Let’s consider two numeric variables and provide comments based on their values:
Sub Select_Case_NumericVariables()
Dim x As Integer
Dim y As Integer
x = 5
y = 10
Select Case True
Case x = 5 And y = 10
MsgBox "x is 5 and y is 10"
Case x = 5 And y <> 10
MsgBox "x is 5 and y is not 10"
Case x <> 5 And y = 10
MsgBox "x is not 5 and y is 10"
Case Else
MsgBox "x is not 5 and y is not 10"
End Select
End Sub
The code evaluates conditions involving both x and y and displays corresponding messages.
2. Using Select Case with String and Numeric Variables
- In the previous example, we used two numeric variables in the Select Case. Now, let’s combine string and numeric data.
- The following code checks both the region and sales amount to provide different messages:
Sub Select_string_and_numeric()
Dim region As String
Dim sales As Double
region = "East"
sales = 75000
Select Case True
Case region = "East" And sales >= 100000
MsgBox "The " & region & " region is performing excellently."
Case region = "East" And sales >= 75000 And sales < 100000
MsgBox "The " & region & " region is performing well."
Case region = "East" And sales < 75000
MsgBox "The " & region & " region needs improvement."
Case region = "West" And sales >= 150000
MsgBox "The " & region & " region is performing excellently."
Case region = "West" And sales >= 100000 And sales < 150000
MsgBox "The " & region & " region is performing well."
Case region = "West" And sales < 100000
MsgBox "The " & region & " region needs improvement."
Case Else
MsgBox "Invalid region or sales amount entered."
End Select
End Sub
The code evaluates both the region and sales values to determine the appropriate message.
3. Select Case Between Two String Values
- We can also use Select Case with string values.
- In this example, the user inputs a color and size, and the code displays a message based on the combination:
Sub selectcase_multiple_string()
Dim color As String
Dim size As String
color = InputBox("Enter the color of the object:")
size = InputBox("Enter the size of the object:")
Select Case True
Case color = "Red" And size = "Small"
MsgBox "The object is a small red item."
Case color = "Blue" And size = "Medium"
MsgBox "The object is a medium blue item."
Case color = "Green" And size = "Large"
MsgBox "The object is a large green item."
Case Else
MsgBox "The object is not recognized."
End Select
End Sub
The code prompts the user for color and size inputs and responds accordingly.
How to Use Select Case with Multiple Condition in Excel VBA
Checking Odd or Even Values: Suppose we want to determine whether a value in an Excel sheet is odd or even. We can achieve this using the following code snippet:
Sub Multiple_condition()
Dim rng As Range
Set rng = Selection
var = rng.Cells(1, 1).Value
Select Case var
Case 1, 3, 5, 7, 9
MsgBox "You have purchased odd number of products"
Case 2, 4, 6, 8
MsgBox "You have purchased even number of products"
End Select
End Sub
- We declare a Range variable named rng and set it to the currently selected range in the worksheet.
- The Select Case statement checks whether the value of the variable var matches any of the specified values (1, 3, 5, 7, or 9).
- Depending on the value, it displays a message box indicating whether the purchase is odd or even.
How to Use Case Else To Compare with All Values in Excel VBA
The Case Else syntax in Select Case allows us to handle conditions that are not explicitly defined in the code. For example, let’s check whether the temperature of a certain place is less than 30°C:
Sub Case_else_compare()
Dim temp As Integer
input1 = InputBox("Enter the current temperature in degree celsius:")
Select Case input1
Case Is < 30
MsgBox "Comfortable Weather"
Case Else
MsgBox "Hot Weather"
End Select
End Sub
- If the input temperature (input1) is less than 30, it displays Comfortable Weather.
- Otherwise, it shows Hot Weather.
How to Use Nested Select Case Statement in Excel VBA
Nested Select Case Example: Suppose we want to determine a student’s course assignment based on their gender and favorite subject. We can achieve this using a nested Select Case structure. Here’s the code:
Sub nested_select()
Dim rng As Range
Set rng = Selection
Select Case rng.Cells.Offset(0, 1).Value
Case "Male"
Select Case rng.Cells.Offset(0, 2).Value
Case "Physics", "Mathematics", "Chemistry"
MsgBox "You should admit in old campus"
Case "Business Studies", "Commerce", "Drawing"
MsgBox "Get admission in next chance"
End Select
Case "Female"
Select Case rng.Cells.Offset(0, 2)
Case "Physics", "Mathematics", "Chemistry"
MsgBox "You may admit into online courses"
Case "Business Studies", "Commerce", "Drawing"
MsgBox "Opportunity available in new campus"
End Select
End Select
End Sub
- We declare a Range variable named rng and set it to the currently selected range in the worksheet.
- The outer Select Case checks whether the student is male or female.
- Depending on the gender, it enters the corresponding inner Select Case.
- The inner Select Case checks the favorite subject and displays an appropriate message.
Output: To view the output, select the name of any student and run the macro code. You’ll immediately see the relevant message based on the student’s gender and subject preference.
Frequently Asked Questions (FAQ)
- What is a Select Case statement?
The Select Case statement is a control structure used in programming to execute different actions based on the value of an expression.
- How can I use a Select Case statement in VBA to select between two values?
You can use a Select Case statement in VBA to choose between two values by specifying two Case statements—one for each value—and a Case Else statement to handle any other values.
- Can I use a Select Case statement in VBA to compare values of different data types?
Yes, you can use a Select Case statement in VBA to compare values of different data types.
Things to Remember
- Specify the expression you want to evaluate in the Select Case statement.
- When using Strings, enclose the string within quotation marks.
- Try to avoid comparing values of different data types unless necessary.
- Add a Case Else statement to handle any values that do not match the specified values.
- Use the Exit Select statement to exit the Select Case block when a match is found.
Download Practice Workbook
You can download the practice workbook from here:
