VBA Switch Function

THE VBA SWITCH function allows us to test multiple conditions without nesting any other function, but we can apply all the logical functions within the same function.

Logical functions are the heart & soul of almost all calculations. Therefore, mastering them will add more value to our skill set CV. Regarding multiple condition tests, logical formulas are inevitable to arrive at the result. For example, we completely rely on the IF function in excel and VBA. As a starter, the IF condition 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.

Syntax

• The Switch function calculates the first expression. If the value is TRUE, it returns the value for the expression. If the value for expression 1 is NOT TRUE, it goes on for the same calculation for expression 2. Moreover, if the result is TRUE, the value 2 is displayed. But if the expression returns as FALSE, the switch moves on to another.

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

Example #1

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, we have declared two VBA variables.

Dim ResultValue As String

Dim FruitName As String

• For the variable "FruitName," we have assigned the fruit name "Apple."

FruitName = "Apple"

• Next, for the variable “ResultValue,” I have assigned the SWITCH. Let me explain to you the function.

Switch(FruitName = "Apple", "Medium",

• It is the first part of the formula. So, for example, if the variable "FruitName" is "Apple," the result should be "Medium."

Next is

FruitName = "Orange", "Cold",

• It is the second part of the basic excel formula. So, for example, if the variable "FruitName" is "Orange," the result should be "Cold."

After that, we wrote.

FruitName = "Sapota", "Heat",

• It is the third part of the formula. For example, if the variable “FruitName” is “Sapota,” the result should be "Heat."

FruitName = "Watermelon", "Cold"

It is the final part of the formula. So, for example, if the variable “FruitName” is “Watermelon,” the result should be "Cold."

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

Since Fruit Name is "Apple," our logical test result is "Medium," and we have a result in the message box.

Assign the different values to the variable "FruitName" to get the individual 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

This time we have assigned city names. Then, according to the city name we supply to the variable "CityName," we 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."

In the above example, we have mentioned the city name as "Delhi," so our result will be "Metro" in the message box in VBA.

Like this, the SWITCH function works in VBA.

• The SWITCH function 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.