Article Summary
Using Excel VBA Select Case streamlines multi-condition logic by replacing tangled nested If statements with clean, readable code. This article covers the syntax, when to use it, and five practical examples — from evaluating expressions and text strings to comparing numbers and ranges. You'll write more efficient VBA confidently.
Visual Basic for Applications or VBA in Excel is a useful built-in programming language that gives us the capability to write our own functions/commands within an Excel worksheet. These functions/commands are useful to develop any kind of functionality in Excel. In this tutorial, we’ll walk you through how to use the ‘Select Case’ conditional statement of VBA in Excel.
We assume that you have prior knowledge of Excel and also know how to use the Visual Basic Editor in MS Excel. If not, we recommend you to take this basic course on MS Excel and also learn how to use the Visual Basic Editor with MS Excel in this course.
If you just need a recap on using VBA, you can run through this VBA tutorial quickly. Microsoft Office applications such as Word, PowerPoint, Excel all understand VBA, and you can use VBA to script with them as well.
When to use Select Case Conditional Statement
Excel VBA Select Case is used in the place of complex Excel Nested If statements. Select Case statement is a better option when we have complex multiple choice cases. The syntax of select case looks like this:
Select Case Condition
Case value_1
Statement_1
Case value_ 2
Statement_2
Case value_ 3
Statement_3
Case value_ x
Statement _x
Case Else
End Select
Here, ‘Condition’ corresponds to the variable or the expression to be evaluated. Depending upon the result of this, the appropriate one of the code segments will be executed. Value_1, Value_2 and so on are the possible values of the ‘Condition.’ Whenever, the ‘Condition’ is satisfied then the code corresponding to the value is executed. Otherwise the code in ‘Case Else’ is executed. Using ‘Case Else’ is not compulsory but recommended as a good programming practice.
Let’s now move on to the practical aspect of this programming construct. We’ll take look at some examples which show multiple ways ‘Select Case’ statement can be used to write programs varying in complexity and scope. Before getting into these programs, you may want to look up this course on how to use Macros in in MSExcel.
Example 1: VBA Select Case with an Expression
Sub Select_Case_Demo()
X = InputBox("Enter the value for X:")
Y = InputBox("Enter the value for Y:")
Select Case X = Y
Case True
MsgBox "The expression is TRUE"
Case False
MsgBox "The expressions is FALSE"
End Select
End Sub
In this program, the InputBox() is used to receive input from the user which is stored in the variables X and Y. In the select case, we evaluate whether variable X has the same value as the variable Y. When the value of the two variables are equal Case True is selected and the MsgBox() displays the string “The expression is TRUE.” If the value is false, Case False is selected and MsgBox() displays the string “The expression is FALSE.”
Example 2: Select Case Statement to Evaluate Text Strings
Sub Select_Case_Demo()
veg_name = InputBox("Enter the vegetable name:")
Select Case veg_name
Case "Cucumber"
MsgBox "You entered Cucumber"
Case "Carrot"
MsgBox "You entered Carrot"
Case "Radish"
MsgBox "You entered Radish"
Case "Beans"
MsgBox "You entered Beans"
Case "Spinach"
MsgBox "You entered Spinach"
Case "Broccoli"
MsgBox "You entered Broccoli"
Case Else
MsgBox "I didn't know this was a veggie!"
End Select
End Sub
In this example, we compare text strings in the Case Statements. If it matches then the corresponding case block will be executed else the “Case Else’ block will be executed.
Example 3: Select Case Statement to Compare Numbers
Sub Select_Case_Demo()
Num = InputBox("Enter any Number between 1 to 50:")
Select Case Num
Case Is < 25
MsgBox "Your Number is less than 25"
Case Is = 25
MsgBox "Your Number is Equal to 25"
Case Is > 25
MsgBox "Your Number is greater than 25"
End Select
End Sub
In this example, we ask the user to input any numbers between 1 and 50. The program will tell us whether the number entered is equal to 25, greater than 25 or less than 25. Note that ‘Is’ keyword is used to compare numerical values. (If you’re not familiar with conditionals in VBA, now would be a good time to check out this Excel VBA course)
Example 4: Select Case Statement to Evaluate Multiple Conditions Under a Single Case
Sub Select_Case_Demo()
Num = InputBox("Enter any Number between 1 to 10:")
Select Case Num
Case 2, 3, 5, 7
MsgBox "Your Number is Prime."
Case 1, 4, 6, 8, 9, 10
MsgBox "Your Number is not Prime."
Case Else
MsgBox "Your Number is out of the range."
End Select
End Sub
In this example, note that we have used “,” to compare multiple conditions within a single case. We enter any number between 1 and 10. The program tells us whether the number entered is a prime number or not. When the number exceeds the range, the appropriate message is displayed.
Example 5: Select Case Statement to Evaluate a Continuous Range as Condition
Sub Select_Case_demo()
Num = InputBox("Enter any Number between 1 to 100:")
Select Case Num
Case 1 To 50
MsgBox "Your Number between 1 to 50"
Case 51 To 100
MsgBox "Your Number between 51 to 100"
Case Else
MsgBox "Your Number is out of the range."
End Select
End Sub
Here, we have given a continuous range as the condition. We enter any value between 1 and 100. If the value is between 1 and 50, including both 1 and 50, the corresponding code will be executed. Similarly, for the values between 51 and 100. If the first two cases are false, then ‘case else’ will be executed.
Note that VBA is different from Macros which could only be used for automating instructions in the older versions of MS Office applications. With VBA today you have the ability to write our own functions or commands to help automate any repetitive tasks. As always practice makes perfect. Work out these examples and also try out other VBA examples with Mr Excel in this course!