VBA Programming : Decision – Select Case

.

Hello friends in the last article of VBA Programming Tutorial you have learnt the use of If..else statement. In this lesson you are going to learn another decision making statements in VBA. It means it is another way to control the flow of program, that is nothing but Select… Case structure.
 

Syntax:


  Select Case <test_expression>

    Case <condition_1>
      <Your code 1>
    Case <condition_2>
      <Your code 2>
    …
    Case <condition_n>
       <your code n>

    Case Else
      <your else code>

  End Select

Where:

Test Expression: is a string or numeric value which you are going to compare with the list of conditions.
Condition 1, 2…n: These are the different conditions. Whichever condition is going to match with the value of Test Expression, Statements under that condition will be executed. Rest other codes in other conditions will be ignored.
Your Code 1, 2…n: is the code which is going to be execute when the condition is found true.
 

Note:

Case Else Statement gets executed when none of the conditions are true for a Test Expression. Note that this Statement is an Optional Statement. It means it is not necessary to put Case Else Statement unless you want to execute a piece of code to be executed even when none of the conditions are matching.

Select Case Statement

Select Case Statement


Use of Case Statement

It is always a good practice to use Select Case Statement when multiple If-Else conditions are involved. As the number of If-Else conditions increases, debugging and understanding all the flow becomes tedious job. To overcome that problem, It is advised to use Select Case statement in nested If-Else conditions.

Example:

I am going to explain different ways of putting the condition expressions in Case Clause. Refer the below 4 examples:
 
To Practice your own, you can download this Excel Workbook with all the four examples.
 
1. Single Expression or value as a Case Condition
In the below example user will be asked to Enter a Number of the Day like 1, 2, 3…7 and using Select Case statement function will return the corresponding Name of the Day. Note: Here first day of the week is considered as “Monday”.



Sub Find_Day_1()
Dim DayNumber As Integer
DayNumber = InputBox("Enter the Day Number")
Select Case DayNumber
    Case 1
        MsgBox ("Monday")
    Case 2
        MsgBox ("Tuesday")
    Case 3
        MsgBox ("Wednesday")
    Case 4
        MsgBox ("Thursday")
    Case 5
        MsgBox ("Friday")
    Case 6
        MsgBox ("Saturday")
    Case 7
        MsgBox ("Sunday")
    Case Else
        MsgBox ("Invalid Day Number")
End Select
End Sub

2. Using a comma to separate multiple expressions or ranges in each Case Clause.

Multiple expressions or ranges can be specified in each Case clause, by separating each expression with a comma It is same as logical operator OR. In the below example in each of the Case clause there are two expression: One is numeric and other one is string. It means if you Input : 1 then Also it will return “Monday” and If you enter “Mon” then also it will return “Monday”.


Sub Find_Day_2()
Dim DayNumber As Variant
DayNumber = InputBox("Enter the Day Number")
Select Case DayNumber
    Case 1, "Mon"
        MsgBox ("Monday")
    Case 2, "Tue"
        MsgBox ("Tuesday")
    Case 3, "Wed"
        MsgBox ("Wednesday")
    Case 4, "Thurs"
        MsgBox ("Thursday")
    Case 5, "Fri"
        MsgBox ("Friday")
    Case 6, "Sat"
        MsgBox ("Saturday")
    Case 7, "Sun"
        MsgBox ("Sunday")
    Case Else
        MsgBox ("Invalid Day Number")
End Select
End Sub

3. Using To Keyword to Specify upper and lower limit of a range.
If you want to compare a value within a range then you can use To Keyword in your case clause as shown in the below example. Below example will take Marks in Percentage as input and it will return the Grade of the Student based on which range does that % belongs to.


Sub Find_Grade()
Dim Percentage As Integer
Percentage = InputBox("Enter the Obtained Percentage Marks by a Student")
Select Case Percentage
    Case 0 To 33
        MsgBox ("Failed")
    Case 33 To 44
        MsgBox ("Third Div")
    Case 45 To 59
        MsgBox ("Second Div")
    Case 60 To 74
        MsgBox ("First Div")
    Case 75 To 100
        MsgBox ("First Div with Distinction")
    Case Else
        MsgBox ("Marks Obtained in % Can not be more than 100")
End Select
End Sub

4. Using the Is Keyword with a comparison operator to compare.
Using Is keyword you can compare the case value using comparison operator like < , > ,=<, >= etc. as shown in the below example:


Sub Find_Temp_Status()
Dim temp As Single
temp = InputBox("Enter the Temperature")
Select Case temp
    Case Is >= 40
        MsgBox "Extremely Hot"
    Case Is >= 25
        MsgBox "Moderately Hot"
    Case Is >= 0
        MsgBox "Cool Weather"
    Case Is < 0
        MsgBox "Extremely Cold"
End Select
End Sub

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Venkat

    Hi Vishwa,

    This is very nice and informative. I like the detailed examples you have given for the article on CASE statement.

    Thanks,

    Venkat.

    Reply
    • Vishwamitra Mishra

      Thanks Venkat !!

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest