EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Logical Functions VBA Select Case
 

VBA Select Case

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated April 8, 2023

VBA Select Case

 

 

VBA Select Case

VBA Select Case is one of the similar condition we use to test multiple conditions instead of the traditional IF condition in VBA. Select Case works exactly the same way as to how IF condition works.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Like how we test multiple conditions and draw results with IF condition similarly using SELECT CASE, we test the condition and draw results. In this article, I will take you through the ideology of the SELECT CASE statement.

Formula of VBA Select Case:

VBA Select Case Formula

How to Use Excel VBA Select Case?

Let’s understand how to use a VBA Select Case Excel function with few examples.

You can download this VBA Select Case Function Template here – VBA Select Case Function Template

VBA Select Case Function – Example #1

Assume you have a value in cell A1 and you want to test whether that value is greater than 100 or not.

VBA Select Case - Cell Value

If the value is greater than 100, we need the result as “More than 100” in cell B1, or else we need the result as “Less than 100”. The below code will do the job for us.

Code:

Sub SelectCase_Ex()

Select Case Range("A1").Value

Case Is > 100
    Range("B1").Value = "More than 100"
Case Else
    Range("B1").Value = "Less than 100"

 End Select

End Sub

VBA Select Case - Code

Run this code using the F5 key or manually as shown to get the results in cell B1.

VBA Select Case - Code Result

VBA Select Case Function – Example #2

Now we will look into nested Select Case statements. Assume you have a loan recovery table from Jan to Dec.

VBA Select Case Statement

In the status section of the data, we need to get the result as follows.

  • If the Recovery Value is more than 45000, then the result should be “Excellent.”
  • If the Recovery Value is more than 40000, then the result should be “Very Good.”
  • If the Recovery Value is more than 30000, then the result should be “Good.”
  • If the Recovery Value is more than 20000, then the result should be “Not Bad.”
  • If all the results are FALSE, then the result should be “Bad.”

Since we need to test multiple cases, we need to use nested Select Case statements much like nested IF statements.

Code:

Sub IF_Results()

Dim i As Integer

i = 2

 For i = 2  To 13

       Select Case Cells(i, 2).Value
           Case Is > 45000
               Cells(i, 3).Value = "Excellent"
           Case Is > 40000
               Cells(i, 3).Value = "Very Good"
           Case Is > 30000
               Cells(i, 3).Value = "Good"
           Case Is > 20000
              Cells(i, 3).Value = "Not Bad"
           Case Else
              Cells(i, 3).Value = "Bad"
      End Select
 Next i

End Sub

VBA Select Case - Code 2

Copy this code and paste it into your module.

Now run this code using the F5 key or manually to get the desired results in the Status column.

Result 2

VBA Select Case Function – Example #3

Now we will see Select Case with our own Input value. We will see how to supply value to an input box, and based on the input value given; we will determine the result.

Code:

Sub SelectCase_InputBox()

Dim MyValue As Integer

MyValue = Application.InputBox("Enter only numerical value", "Enter Number")

Select Case MyValue

        Case Is > 1000
            MsgBox "Entered Value is more than 1000"
        Case Is > 500
            MsgBox "Entered Value is more than 500"
        Case Else
            MsgBox "Entered Value is less than 500"
End Select

End Sub

Code 3

Copy & paste this code to your module.

Step 1: After copying the code to your module, run the code; you will see this input box.

Step 2: Here, we need to enter only numerical values.

Step 3: Now click on OK we will see a message box showing the result of the Select Case statement result.

Select Case Statement - Result

VBA Select Case Function – Example #4

In this example, we will see how to pass a range of numbers as a test to select a case, and based on that; we will draw the results.

Copy & paste the below code to your module to up & running.

Code:

Sub SelectCase()

Dim Mynumber As Integer

Mynumber = Application.InputBox("Enter Number", "Please Enter numbers from 100 to 200")

Select Case Mynumber
        Case 100 To 140
            MsgBox "The number you have entered is less than 140"
       Case 141 To 180
            MsgBox "The number you have entered is less than 180"
       Case Else
            MsgBox "The number you have entered is > 180 & <200"
End Select

End Sub

Code 4

Step 1: Run this code; you will see the Input Box.

Step 2: As the prompt says, we need to enter the numbers from 100 to 200. So enter the numbers between 100 to 200.

Step 3: Click on OK. We will see the result based on the select case test result.

Result

Things to Remember

  • Unlike in IF condition test will not go through all the conditions in Select Case. As soon as the condition is met, it will exit the other conditions.
  • Like all the other conditions here also all the text values should be in double-quotes.
  • This works exactly similar to the IF statement in VBA. This is a kind of alternative to the IF statement.

Recommended Articles

This has been a guide to VBA Select Case Function. Here we discussed VBA Select Case and how to use Excel VBA Select Case Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles to learn more –

  1. VBA Loops
  2. VBA Function
  3. VBA VLOOKUP
  4. VBA XML

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Select Case Function Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW