Excel VBA Switch Case
Switch Case or Select Case is a statement available in VBA to conduct logical tests where it works as an alternative to IF-THEN statement in VBA. Using a Switch Case, we can conduct multiple logical tests and arrive results based on multiple results.
Below is the syntax of the Switch Case/Select Case statement.
Select Case < Logical Test > Case 1 < Logical Test > Value if Case 1 Test is TRUE Case 2 < Logical Test > Value if Case 2 Test is TRUE Case 3 < Logical Test > Value if Case 3 Test is TRUE Case Else Value if none of the above cases are TRUE End Select
< Logical Test >: What is the logical test? We need to enter the test here.
Case 1, Case 2: In each case, we need to test multiple logical tests in excelLogical Tests In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test..
How to Use VBA Switch Case Statement?
In cell A1 I have entered the value as 550.
We will test this number using switch case statements and arrive at the status as “More than 500” if the value is more than 500 or else we will arrive at the status as “Less than 500”.
Open the VBA Sub procedureVBA Sub ProcedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA. first.
Sub Switch_Case() End Sub
Open Select Case Statement in VBASelect Case Statement In VBAVBA Case Statement is a function that assists the users to execute multiple logical tests and arrive at results in two ways, i.e. first, if the result or logical test is TRUE and second, if the result or logical test is FALSE. and supply the logical test Range(“A2”).value
Sub Switch_Case() Select CaseSelect CaseA select case is a substitute for writing down multiple IF statements in VBA. In the case of many IF statements, it becomes tedious and more complex, but in select case statements, we define the criteria as different cases and results. Range("A2").Value End Sub
Now enter the first case as Case is >500.
Sub Switch_Case() Select CaseSelect CaseA select case is a substitute for writing down multiple IF statements in VBA. In the case of many IF statements, it becomes tedious and more complex, but in select case statements, we define the criteria as different cases and results. Range("A2").Value Case Is > 500 End Sub
If this case is TRUE, then what is the result we need in cell B,2, i.e., “More Than 500”.
Sub Switch_Case() Select Case Range("A2").Value Case Is > 500 Range("B2").Value = "More than 500" End Sub
Now we are left with only one result, i.e., Case Else statement. If the first Case is FALSE, then we need the result as “Less than 500”.
Sub Switch_Case() Select Case Range("A2").Value Case Is > 500 Range("B2").Value = "More than 500" Case Else Range("B2").Value = "Less than 500" End Sub
Now close the statement by using the “End Select” statement.
Sub Switch_Case() Select Case Range("A2").Value Case Is > 500 Range("B2").Value = "More than 500" Case Else Range("B2").Value = "Less than 500" End Select End Sub
Run the code we will get the value in cell B2.
Since the value in cell A2 is greater than 500, we got the result as “More than 500”.
Now we will see using more case examples. Below is the score of the student in the examination.
With this score, we need to arrive at Grade, for that below is the criteria.
- Score >=85, Grade = “Dist”
- Score >=60, Grade = “First”
- Score >=50, Grade = “Second”
- Score >=35, Grade = “Pass”
- If anything else Grade = “Fail.”
Dim Score As Integer Score = 65 Select Case Score Case Is >= 85 MsgBox "Dist" Case Is >= 60 MsgBox "First" Case Is >= 50 MsgBox "Second" Case Is >= 35 MsgBox "Pass" Case Else MsgBox "Fail" End Select End Sub
Run this code. We will get the grade in the message box.
Since the score is more than 60 but less than 85 grade is “First.”
We have seen how to find a grade for one student, what about finding a grade for more than one student. Below are the scores of students.
Since more than one student is involved, we need to enclose FOR NEXT loop in VBAFOR NEXT Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. . Below is the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
Sub Switch_Case2() Dim k As Integer For k = 2 To 7 Select Case Cells(k, 2).Value Case Is >= 85 Cells(k, 3).Value = "Dist" Case Is >= 60 Cells(k, 3).Value = "First" Case Is >= 50 Cells(k, 3).Value = "Second" Case Is >= 35 Cells(k, 3).Value = "Pass" Case Else Cells(k, 3).Value = "Fail" End Select Next k End Sub
Run this code. We will get grades.
Things to Remember
- Switch Case is often referred to as “Select Case.”
- The switch is a functionSwitch Is A FunctionSwitch function in excel is a comparison and referencing function which compares and matches a referred cell to a group of cells and returns the result based on the first match found. The method to use this function is =SWITCH( target cell, value 1, result 1….)., not a statement.
- If no logical tests are TRUE, then you can simply pass the alternative result in the CASE ELSE statement and always close the statement with the “END SELECT” word.
This has been a guide to VBA Switch Case. Here we discuss how to use switch-case statements in excel VBA along with examples and downloadable excel templates. Below are some useful articles related to VBA –