In this article, we will discuss the use of Excel VBA Select Case to pick between two values. We will explain the syntax of the Select Case and use it for different scenarios like: between two numeric values, between two ranges, two strings, and so on.
Overview of Select Case Statement in Excel VBA
Select Case is a control structure in VBA (Visual Basic for Applications) that allows the user to test a variable or expression for multiple possible values and execute different code blocks depending on the value.
Select Case expression
Case value1
' do something if expression equals value1
Case value2
' do something if expression equals value2
Case Else
' do something if expression does not match any previous cases
End SelectThe Case statements list the possible values that the expression can have, and if the expression matches that value, the code under that case runs. The Case Else statement executes if the expression does not match any of the previous cases.
Why We Use Select Case Statement?
We can use Select Case in Excel VBA instead of If…..Then when there are multiple conditions that need to be checked against a single variable or expression. When the point is to increase the readability of a code, “Select Case” is surely preferable to “If…Then”. “Select Case” makes it easier to maintain code over time because it is easier to add or remove conditions from the “Select Case” statement than the “If….Then” statements. Apart from that, “Select Case” is also more efficient than the “If…..Then” statement. This is because “Select Case” evaluates the expression once and then compares it to the different cases, whereas “If…Then” evaluates the expression for each condition.
Excel VBA Select Case Between Two Values: 6 Examples
In this section, we will demonstrate six practical examples of using Select Case Between Two Values in Excel VBA appropriate illustrations.
1. Select Case Between Two Numeric Values
We can use “Select Case” either to compare exact values or to compare cases with inequality. First, we will see the way of using exact values in Select Case.
1.1 Comparing with Exact Values
In this example, we use the select case to compare exact values. The user will give a number(1 or 2) depending on the condition. Based on the value that the user selects, the code will do different types of works. The following code will help us do that.
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 SubThe above code in Editor looks like the image below.
🔎 How Does the Code Work?
Select Case AgreeThe code will evaluate the value of “Agree” and execute different operations based on this value.
Case 1
MsgBox "Yes! Proceed please"When the value of “Agree” is 1, code will show a MsgBox containing the value “Sorry! Access Denied”.
Case 2
MsgBox "Sorry! Access Denied"If the user inputs 2 instead of 1, a MsgBox appears with the message “Sorry! Access Denied”.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. An InputBox appears and we put the value “1” in it.
As a result, we see the following output.
1.2 Comparing Cases with Inequalities
It is also possible to use inequality in Select Case. We have to use “Case Is” syntax this time instead of “Case”. A very simple code to do that is as follows.
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 SubWhen we write the code in “VBA Editor” it looks like below.
🔎 How Does the Code Work?
num = InputBox("Enter your number:")Prompts the user to give his obtained number.
Case Is >= 80
MsgBox "You got A in the exam"This line checks whether the input value by the user is greater than or equal to 80. If true, then the program executes the next line.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. An InputBox appears and we put “55” in it.
Pressing ENTER, we will see the following result.
Read More: How to Use VBA Case Statement
2. Select Case Between Two Ranges
It is possible to use range in the Excel VBA Select Case. In the following code, we will check whether a patient is in a normal condition or in a feverish condition using Excel VBA Select Case between two ranges.
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 SubWriting the code in the Editor, we will find a view like the image below.
🔎 How Does the Code Work?
Set rng = Application.InputBox("Select the patient temperature:", Type:=8This line of code asks the user to select a range of cell that contains patient temperature and assigns that to the variable “rng”.
“Type:=8” : is an optional argument that specifies that the user should select a range of cells.
Case 95 To 99.5Checks if the cell selected by the user contains a value between 95-99.5
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. An InputBox appears and we put “97.2” in it and press ENTER. As a result, we will see the following result.
Read More: Excel VBA Select Case True
3. Select Case Between Two String Values
Excel VBA Select Case is useful to choose between string data also. In the case of using “Select Case” the string must be in a double quotation mark. The following code will choose a month and determine the season associated with it.
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 SubThe above code in the Editor will look like the image below.
🔎 How Does the Code Work?
str1 = Application.InputBox("Give a string value:", Type:=2)Displays an input box to the user and assigns the value inserted by the user to a variable called “str1” as a string (Type:=2 is to store the value as a string).
Select Case TrueEvaluates different conditions and executes different blocks of code depending on which condition is true.
Case str1 < str2
MsgBox str1 & " comes before " & str2Checks whether the first input string is less than the second one. If True, then it shows the defined MsgBox.
Execution and Output
In order to view the output, we will open the Macro window from the Developer tab and input 2 values in the InputBox one by one. The code will compare them and give an output based on the value we input. In our case, the inputs were “NewYork” and “Jamaica”. As a result, we see the following image as output.
4. Select Case Between Two Values with Colon Operator
It is possible to use “Colon operator” combined with a range in Select Case. In the following example, we will check whether the speed of a car falls in a certain range and give a certain message to the driver using 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 above code in the Editor will look like the image below.
🔎 How Does the Code Work?
Dim speed As DoubleThis line declares the variable “speed” as double as we assume that speed may be both integer or a floating point number.
Case 0 To 40:
MsgBox "Slow or Moderate speed"The block of code above will be executed if the value being tested in the Select Case statement is between 0 and 40 and shows a MsgBox containing the message “Slow or Moderate speed”.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. In the InputBox, we enter the value “25”.
As a result, we will see the following result.
5. Select Case Between Two Values in a Custom Function
Writing code to execute a specific work and then assigning it to a function is a nice way to check whether a value falls in a certain range. In the following example, we will write a code in Excel VBA and assign the code to a custom function named “Grade”. We are going to apply that in the following dataset.
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 FunctionVBA Editor with the code will look like below.
🔎 How Does the Code Work?
Function Grade(number As Integer)Declares a function called “Grade” with one input parameter called “number” of data type Integer.
Case 0 To 32
comment = "Fail"The code will assign the string “Fail” to the variable comment if the value of the variable number falls between 0 to 32.
Grade = commentAfter the execution of Select Case  and before ending the Function, the code will assign the value of comment to Grade and return the value where it is called.
Execution and Output
After saving the code, Excel will create the UDF to let us use it in the workbook. We can now use the following function in cell D5 to get the result.
=Grade(C5)
Read More: How to Use Excel VBA Select Case and Operator
6. Compare Two Values Using Case Is
In a normal Excel VBA Select Case structure, there is no way of using inequalities. When we need to use inequalities, we may use Case Is syntax to do that. In the following example, we will use Case Is to check whether the temperature of a certain place is above or below the 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 SubIf you write the code in VBA Editor,it looks like the image below.
🔎 How Does the Code Work?
var1 = num.Offset(0, 1).ValueThis line assigns to “var1” the value of the cell that is one column to the right of the first cell in the num range.
Select Case var1The Select Case statement compares the value of “var1” to the list of cases provided in subsequent lines of code.
Case Is <= 25
num.Offset(0, 2).Value = "Below Room Temperature"The first line of the code checks if the value of var1 is less than or equal to 25. In this case, it sets the value of a cell that is two columns to the right of the num range to the text string “Below Room Temperature”.
Execution and Output
With a view to seeing the output, we select the name of a location and run the Macro named “Case Is”.Doing that we will see the following output.
How to Use Select Case Statement with Multiple Variables in Excel VBA
We can use this “Select Case” for multiple variables in Excel VBA. In the following examples, we will demonstrate this criterion with examples. We check two numeric variables and comment on them in the first one. For the second case, we use multiple variables in Excel VBA to generate the grade of a student. In the last case, we will use Select Case between 2 string variables.
1. Multiple Variables Numeric Values with Select Case
In the following code, we will see how to check whether a variable is equal, less than, or greater than a numeric value.
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 SubWhen we write the code in the editor, it looks like the image below.
🔎 How Does the Code Work?
Dim x As IntegerWe are declaring a variable named “x” with the data type Integer.
x=5Assigns the value 5 to the variable x.
Select Case True
When we use Select Case True, it sets up a series of conditions that will always be evaluated because True always evaluates to True. This is useful when we want to evaluate multiple conditions that involve the values of multiple variables.
Case x = 5 And y <> 10In this line, we are defining a condition to be evaluated. This condition consists of two sub-conditions joined by the And operator, which means that both sub-conditions must be True for the overall condition to be True.
Execution and Output
We see the output by pressing “F5”. The output of the code looks like the image below.
We set the value of x to 5 and y to 10. So, the first select case was executed and the corresponding MsgBox is shown in the output.
2. Select Case with String and Numeric Variables
In the example above, we used 2 numeric variables in the select case. But in the following example, we will use string and numeric type data. The code is given below.
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 SubThe above code in the editor looks like the image below.
🔎 How Does the Code Work?
 Case region = "West" And sales >= 100000 And sales < 150000This line checks whether the value of variable “region” is “West” and the sales value is between 100000-1500000. If both the cases become true, the code executes the next line or jumps to check the next select case.
 Case Else
     MsgBox "Invalid region or sales amount entered."If none of the condition above are satisfied, a MsgBox will appear showing the message that invalid region or sales amount.
Execution and Output
In order to view the output of the above code, we run it by clicking the “Run” button in the editor. The output of the code looks like the image below.
We defined the variable region as “East” and sales tp “75000”. The select case corresponding to these values is the second one. That’s why we see the MsgBox corresponding to the second select case.
3. Select Case Between 2 String Values
We can use the select case between 2 string values also. In the following example, we will demonstrate a way that takes 2 inputs from the user and based on that value, it shows a MsgBox. The VBA code is given below.
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 SubAfter writing the code in the editor, you will find the editor looking like the image below.
🔎 How Does the Code Work?
color = InputBox("Enter the color of the object:")Prompts the user to enter the color of an object. The user’s input will be stored in the “color” variable, which is declared as a string variable.
Case color = "Red" And size = "Small"It checks whether the color input given by the user is “Red” and size input is “Small”.
Execution of Code and Viewing Output
As we wish to view the output of the code, we run it by clicking the run button. An InputBox appears and we put “Blue” in the box.
When we click “Ok”, a new InputBox appears prompting us to give the size of the object. We put “Medium” in that box.
Finally, when you click “Ok” you will get the MsgBox like the following image.
How to Use Select Case with Multiple Condition in Excel VBA
It is possible to check conditions using Select Case not only for a single one but also for multiple conditions. Suppose we want to check whether a value in an Excel sheet is odd or even. We may use the following code snippet to do that.
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 SubIf we write the code in the editor, it looks like below.
🔎 How Does the Code Work?
Dim rng As RangeDeclaring “rng” as a Range variable.
Set rng = SelectionAssigns the currently selected range in a worksheet to the variable “rng”.
Select Case var
Case 1, 3, 5, 7, 9These lines check whether the value of the variable “var” is equal to any of the values “1,3,5,7 or 9”.
Execution and Output
In the Excel Sheet, we select the value that we want to check for odd or even. Doing that we select the right macro and run that. The Output will be as follows.
How to Use Case Else To Compare with All Values in Excel VBA
Case Else Syntax in Select Case is a great option to compare all the conditions that are not specified in the code. In the following example, we will check whether the temperature of a certain place is less than 30 or not. The code to do that is as below.
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 SubThe image below depicts how the code editor appears.
🔎 How Does the Code Work?
Case Is < 30
MsgBox "Comfortable Weather"The above lines are to check the value of “input1”.If the value is below 30,the next line will be executed.
Case Else
MsgBox "Hot Weather"The code is checking for a condition that is not explicitly defined in the other Case statement in the Select Case block. If the other case isn’t true, the Editor will execute the code in the Case Else block.
Viewing Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab.We input the value “35” in the InputBox.
As a result, we will see the following result.
How to Use Nested Select Case Statement in Excel VBA
Like For loop, it is possible to create a nested Select Case. When we want to check more than one condition, we may use a nested Select Case. In the following example, first, we will check whether a student is male or female. Then we check the favorite subject of the student and assign him/her to a course.
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 SubWe write the code in the VBA Editor and the editor looks like the image below.
🔎 How Does the Code Work?
Select Case rng.Cells.Offset(0, 1).ValueIt is the outer Select Case. It offsets the current selection by one column and checks the possible conditions.
Case "Male"
Some operations
Case "Female"
Some operationsThe above codes are the conditions under the first Select Case.
Select Case rng.Cells.Offset(0, 2)This line defines the inner Select Case. The code offsets the current selection by 2 columns right. The value of that cell is the argument of Select Case.
Case "Physics", "Mathematics", "Chemistry"
MsgBox "You may admit into online courses"
Case "Business Studies", "Commerce", "Drawing"
MsgBox "Opportunity available in new campus"These Case conditions are defined for the inner Select Case.
Output of Code
To view the output of the code, we select the name of any student and then run the Macro code. Immediately we will see the output as follows.
Read More: How to Use Nested Select Case with VBA in Excel
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 select 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
- Specifying the expression that we want to evaluate in the Select Case statement.
- While using String, the string must be within the quotation mark.
- Trying to avoid comparing values of different data types unless necessary.
- Adding a Case Else statement to handle any values that do not match the specified values.
- Exit Select statement to exit the Select Case block when a match is found.
Download Practice Workbook
You may download the following workbook to practice yourself.
Conclusion
That is the end of this article regarding the Excel VBA Select Case between two values. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.
