WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA Switch Function

Excel VBA Switch Function

VBA SWITCH function allow us to test multiple conditions without nesting any further function but we can apply all the logical functions within the same function itself.

Logical functions are the heart & soul of almost all the calculations. Mastering them will add more value to our skill set CV. When it comes to multiple condition tests, logical formulas are inevitable to arrive at the result. We completely rely on the IF function in excel & VBA. As a starter, the IF condition itself is tough to digest, but when we say nested IF, it is a herculean formula to master. However, to overcome all those nested formulas, we have a formula called SWITCH.

VBA-Switch

Syntax

vba switch syntax

  • Switch calculates the first expression, and if the value is true, it returns the value for the expression, and if the value for expression 1 is not true, it goes on for the same calculation for expression 2, and if the result is true, then the value 2 is displayed but if the expression is returned as false, switch moves on to another expression.

What if none of the expression is true and all are returned as false? In such a situation, we get run time error unless we have a pre-emptive error handling for such situations.

How to use the Switch Function in VBA?

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

Example #1

Now, look at the below code.

Code:

Sub Switch_Example1()

    Dim ResultValue As String
    Dim FruitName As String

    FruitName = "Apple"
    ResultValue = Switch(FruitName = "Apple", "Medium", FruitName = "Orange", "Cold", FruitName = "Sapota",  
    "Heat", FruitName = "Watermelon", "Cold")

    MsgBox ResultValue

End Sub

vba switch example 1.1

  • In the above code, I have declared two VBA variables.
Dim ResultValue As String

Dim FruitName As String
  • For the variable FruitName, I have assigned the fruit name as “Apple.”
FruitName = "Apple"
  • Next, for the variable “ResultValue,” I have assigned the SWITCH. Let me explain to you the function.
Switch(FruitName = "Apple", "Medium",
  • This is the first part of the formula. If the variable FruitName is Apple, then the result should be “Medium.”

Next is

FruitName = "Orange", "Cold",
  • This is the second part of the basic excel formula. If the variable FruitName is Orange, then the result should be “Cold.”

After that, we have written.

FruitName = "Sapota", "Heat",
  • This is the third part of the formula. If the variable FruitName is Sapota, then the result should be “Heat.”
FruitName = "Watermelon", "Cold"

This is the final part of the formula. If the variable FruitName is Watermelon, then the result should be “Cold.”

When I run this code, it will return the result as “Medium” because for the variable “FruitName,” I have assigned the value as “Apple.”

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

vba switch example 1.2

Since Fruit Name is “Apple,” our logical test result is “Medium,” so accordingly, we have a result in the message box.

Assign the different values to the variable “FruitName” to get the respective result.

Example #2

Take a look at one more example.

Code:

Sub Switch_Example2()

    Dim ResultValue As String
    Dim CityName As String

    CityName = "Delhi"
    ResultValue = Switch(CityName = "Delhi", "Metro", CityName = "Bangalore", "Non Metro", CityName = "Mumbai", 
    "Metro", CityName = "Kolkata", "Non Metro")

   MsgBox ResultValue

End Sub

Example 2.1

This time I have assigned city names, whatever the city name we supply to the variable “CityName,” accordingly, I applied some results to the respective city names.

If you apply the city name as either “Delhi or Mumbai,” we get the result as “Metro,” or if we apply the city name as either “Bangalore or Kolkata,” we get the result as “Non-Metro.”

Now in the above example, I have mentioned the city name as “Delhi,” so our result will be “Metro” in the message box in VBA.

Example 2.2

This is how the SWITCH function works in VBA.

Things to Remember

  • SWITCH is available as both excel worksheets and VBA functions.
  • We can use SWITCH as an alternative to nested IF conditions Excel.
  • Use this in real examples to get practical problem-solving.

Recommended Articles

This has been a guide to VBA Switch. Here we discuss what does switch function does in VBA along with examples and a downloadable excel template. You can learn more from the following articles –

  • Excel Sentence Case
  • VBA Solver
  • Select Case in Excel VBA
  • SWITCH Function in Excel
5 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ 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 Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More