VBA Switch

Switch Function in VBA

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 IF function in excel & VBA. As a starter, 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 excel & VBA has a formula called the “SWITCH” function. In this article, we will introduce you to the SWITCH function in VBA.

What Does Switch Function Do in VBA?

If you have used nested IF conditions then the VBA Switch function is ready to replace the complicated nested IF statements.

Yes!!! VBA SWITCH functions allow us to test multiple conditions without nesting any further function but we can apply all the logical functions within the same SWITCH function itself. Below is the syntax of the SWITCH function in VBA.

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 same calculation for expression 2 and if the result is true then value 2 is displayed but if the expression is returned is 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.

Examples of Switch Function in VBA

Below are some examples of switch function in excel VBA.

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

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 function. Let me explain to you the VBA SWITCH 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 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”.

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 of the VBA SWITCH function now.

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

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.

This is how the SWITCH function works in VBA.

Things to Remember

• SWITCH function is available as both worksheet function and VBA function.
• We can use SWITCH as an alternative to nested IF conditions.
• 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 excel with examples and downloadable excel sheet. You can learn more from the following articles –

• Excel Sentence Case
• Examples of Resize Property in VBA
• Use of VBA LOOKUP
• CDate Function VBA Examples
• Excel VBA Solver
• Match Function in Excel VBA
• Select Case in Excel VBA
• SWITCH Function in Excel
• List of Advanced Excel Formulas