Excel VBA Select Case: Use VBA to Automatically Make Selections for You!

excel vba select caseVisual 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!