WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Switch Case

VBA Switch Case

Excel VBA Switch Case

Switch Case or Select Case is a statement available in VBA to conduct logical tests where it works as an alternative to IF-THEN statement in VBA. Using a Switch Case, we can conduct multiple logical tests and arrive results based on multiple results.

Below is the syntax of the Switch Case/Select Case statement.

Code:

Select Case < Logical Test >
Case 1 < Logical Test >
Value if Case 1 Test is TRUE
Case 2 < Logical Test >
Value if Case 2 Test is TRUE
Case 3 < Logical Test >
Value if Case 3 Test is TRUE
Case Else
Value if none of the above cases are TRUE
End Select

< Logical Test >: What is the logical test? We need to enter the test here.

Case 1, Case 2: In each case, we need to test multiple logical tests in excel.

How to Use VBA Switch Case Statement?

You can download this VBA Switch Case Excel Template here – VBA Switch Case Excel Template

Example #1

In cell A1 I have entered the value as 550.

VBA Switch Case Example 1

We will test this number using switch case statements and arrive at the status as “More than 500” if the value is more than 500 or else we will arrive at the status as “Less than 500”.

Open the VBA Sub procedure first.

Code:

Sub Switch_Case()

End Sub

VBA Switch Case Example 1-1

Open Select Case Statement in VBA and supply the logical test Range(“A2”).value

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Switch_Case()

  Select Case Range("A2").Value

End Sub

VBA Switch Case Example 1-2

Now enter the first case as Case is >500.

Code:

Sub Switch_Case()

  Select Case Range("A2").Value
  Case Is > 500

End Sub

VBA Switch Case Example 1-3

If this case is TRUE, then what is the result we need in cell B,2, i.e., “More Than 500”.

Code:

Sub Switch_Case()

 Select Case Range("A2").Value
  Case Is > 500
   Range("B2").Value = "More than 500"

End Sub

VBA Switch Case Example 1-4

Now we are left with only one result, i.e., Case Else statement. If the first Case is FALSE, then we need the result as “Less than 500”.

Code:

Sub Switch_Case()

 Select Case Range("A2").Value
  Case Is > 500
    Range("B2").Value = "More than 500"
  Case Else
    Range("B2").Value = "Less than 500"

End Sub

VBA Switch Case Example 1-5

Now close the statement by using the “End Select” statement.

Code:

Sub Switch_Case()

 Select Case Range("A2").Value
  Case Is > 500
   Range("B2").Value = "More than 500"
  Case Else
   Range("B2").Value = "Less than 500"
 End Select

End Sub

VBA Switch Case Example 1-6

Run the code we will get the value in cell B2.

VBA Switch Case Example 1-7

Since the value in cell A2 is greater than 500, we got the result as “More than 500”.

Example #2

Now we will see using more case examples. Below is the score of the student in the examination.

Example 2

With this score, we need to arrive at Grade, for that below is the criteria.

  • Score >=85, Grade = “Dist”
  • Score >=60, Grade = “First”
  • Score >=50, Grade = “Second”
  • Score >=35, Grade = “Pass”
  • If anything else Grade = “Fail.”

Code:

Sub Switch_Case1()

 Dim Score As Integer

 Score = 65

 Select Case Score
  Case Is >= 85
   MsgBox "Dist"
  Case Is >= 60
   MsgBox "First"
  Case Is >= 50
   MsgBox "Second"
  Case Is >= 35
   MsgBox "Pass"
  Case Else
   MsgBox "Fail"
End Select

End Sub

Run this code. We will get the grade in the message box.

Example 2-1

Since the score is more than 60 but less than 85 grade is “First.”

Example #3

We have seen how to find a grade for one student, what about finding a grade for more than one student. Below are the scores of students.

Example 3

Since more than one student is involved, we need to enclose FOR NEXT loop in VBA. Below is the VBA code.

Code:

Sub Switch_Case2()

 Dim k As Integer

 For k = 2 To 7
  Select Case Cells(k, 2).Value
   Case Is >= 85
    Cells(k, 3).Value = "Dist"
   Case Is >= 60
    Cells(k, 3).Value = "First"
   Case Is >= 50
    Cells(k, 3).Value = "Second"
   Case Is >= 35
    Cells(k, 3).Value = "Pass"
   Case Else
    Cells(k, 3).Value = "Fail"
 End Select
 Next k

End Sub

Run this code. We will get grades.

Example 3-1

Things to Remember

  • Switch Case is often referred to as “Select Case.”
  • The switch is a function, not a statement.
  • If no logical tests are TRUE, then you can simply pass the alternative result in the CASE ELSE statement and always close the statement with the “END SELECT” word.

Recommended Articles

This has been a guide to VBA Switch Case. Here we discuss how to use switch-case statements in excel VBA along with examples and downloadable excel templates. Below are some useful articles related to VBA –

  • Case Statement in Excel VBA
  • VBA UCase
  • Index Match in VBA
  • IF OR in VBA
6 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Switch Case Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More