VBA Switch Function

VBA SWITCH function allows us to test multiple conditions without the need for complicated nested IF functions. The VBA Switch function evaluates a list of conditions or expressions and returns the value associated with the first expression that evaluates to True.

Logical functions are the heart & soul of almost all calculations. Therefore, mastering them is very important.  For multiple condition tests, logical formulas are unavoidable to arrive at the right solution.. For example, we completely rely on the IF function in Excel and VBA.

The VBA Switch function simplifies the code when you have to test for multiple conditions. It makes the code clear and more readable. It is a great substitute for multiple If...ElseIf statements. It returns the result for the first condition that is True. . However, to overcome the difficulties of using complex  nested IF formulas, we use the VBA SWITCH function.

Syntax

The syntax of the VBA Switch function is as follows.

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.

Based on the above syntax, one can use the Switch function to evaluate multiple conditions easily  and return a corresponding value. Let us look at some suitable examples as shown below. For detailed understanding, you can consider enrolling for our VBA Crash Course for Beginners. 

Example #1

Look at the code below. We will try to explain the entire0 code to you and what this code does.

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,” we have used the SWITCH function. Let us explain what it does. 

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

• It is the first part of the formula. So, for example, if the variable "FruitName" is assigned the value "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." For the uninitiated, Sapota is a sweet tropical fruit with a soft, brown skin and is called sapodilla in many countries. 

FruitName = "Watermelon", "Cold"

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

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

Assign different fruit names to the variable "FruitName" to get the results accordingly.

Example #2

Take a look at one more example. We have some code written down. Let us examine it step-by-step. 

Code:

Sub Switch_Example2()

    Dim ResultValue As String

    Dim CityName As String

    CityName = "New York"

    ResultValue = Switch( _

        CityName = "New York", "USA", _

        CityName = "Ottawa", "Canada", _

        CityName = "Chicago", "USA", _

        CityName = "Beijing", "China" _

    )

    MsgBox ResultValue

End Sub

This time we have used city names. 

Based on the  city name we supply to the variable "CityName," we get the results of which country they belong to using the VBA Switch function.

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

Thus, The Switch function evaluates expressions in order and returns the result corresponding to the first True expression.

Interested in learning more on VBA functions? You can check out our Basic VBA Course here to start your journey into the exciting world of VBA programming.

• 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.