Excel VBA Switch 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 excelIF Function In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Switch Function (wallstreetmojo.com)

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.

How to use the Switch Function in VBA?

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 variablesDeclared Two VBA VariablesVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more.

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 formulaBasic Excel FormulaThe term “basic excel formula” refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more. 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.

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 VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Like this, the SWITCH function works in VBA.

Things to Remember

  • The SWITCH function is available as both Excel worksheets and VBA functionsVBA FunctionsVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.We can use SWITCH as an alternative to nested IF conditions ExcelNested IF Conditions ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more.Use this in real examples to get practical problem-solving.

This article has been a guide to VBA Switch. Here, we discuss what the Switch function does in VBA, examples, and a downloadable Excel template. You can learn more from the following articles: –

  • Excel Sentence CaseVBA SolverSelect Case in Excel VBASWITCH Function in Excel