In Visual Basic for Applications (VBA), when a user needs to execute a series of statements depending on the value of an expression, they turn to the versatile tool known as the "Switch Case." This construct allows programmers to define multiple cases, each representing a different value, and then "switch on" the variable based on these cases. When the test expression aligns with one of the specified cases, the corresponding set of statements is executed. However, in situations where none of the defined cases match the test expression, VBA has a fallback option: the "Case Else" statement, which serves as a contingency plan for handling unanticipated scenarios. In this article, we will delve deeper into the intricacies of the VBA Switch Statement, exploring its practical applications and how it simplifies decision-making in VBA programming.
Note: We can use a Select case instead of using multiple if-then statements in Excel VBA. In the below example, the user will enter the student mark in “Cell C5” as input. Macro read C5 cell value and fill grade in “Cell D5”.
What is a VBA Switch Case Statement in Excel
Excel VBA (Visual Basic for Applications) doesn't have a built-in "switch-case" statement like some other programming languages, such as C++ or JavaScript. However, you can achieve similar functionality in VBA using the "Select Case" statement. The "Select Case" statement allows you to test a variable or expression against a list of values and execute different code blocks based on the matched value.
How to Use Select Case Statements in Excel VBA
In Excel VBA, you can leverage the power of the 'Select Case' statement to simplify and streamline your decision-making process. This structured approach is particularly useful when you have multiple conditions to evaluate and corresponding actions to take.
Syntax of the Select Case Statement
The Switch Statement in Excel VBA provides a concise and structured way to evaluate multiple expressions and determine the corresponding values based on their truthfulness. It essentially acts as a decision-making tool that efficiently handles a list of expressions, returning the value associated with the first expression found to be true. This Syntax involves the use of pairs, where each pair consists of an expression and the value to be returned if that expression evaluates to true.
Syntax:
Select Case Expression
Case expressionList1
Statement1
Statement2
...
...
Statement1n
Case expressionList2
Statement1
Statement2
...
...
Case expressionlistn
Statement1
Statement2
...
...
Case Else
elsestatement1
elsestatemnet2
...
...
End Select
VBA Switch Statement Example
Case: Calculating Grades Based on Student Marks
Grade Criteria
- <35 – Fail
- 35-60 – Grade C
- 60-80 – Grade B
- >=80 – Grade A
Presently, the VBA code will extract the value from cell C5, and it will employ a Select Case statement to establish criteria for assigning a student's grade.
Declaring Variables
|
| mark | Integer | read cell C5 Value |
| result | String | write in cell D5 |
'variable declaration
Dim mark As Integer, result As String
Initialize variable
'initialize variable
mark = Range("C5").Value
Select case... - check the mark variable and assign a respective grade to the variable result
'select case statement to check mark and execute case statement
Select Case mark
Case Is >= 80
result = "Grade A"
Case Is >= 60
result = "Grade B"
Case Is >= 35
result = "Grade C"
Case Else
result = "FAIL"
End Select
Write the Grade to cell D5
Range("D5").Value = result
How to Calculate Grades Based on Student Marks in VBA Excel
Step 1: Create a Template
Step 2: Add the Update Grade Shape
Step 3: Right-click on Update Grade and Assign Macro
Step 4: Select the Update Grade Macro and Click Ok
Select “updateGrade”, you can see a list of available macros in your workbook
Step 5: Save your Excel File
Save your Excel file as “Excel Macro-Enabled Workbook” *.xlsm
Step 6: Click Update Grade
Click “Update Grade” to execute the VBA code and see the output in cell D5.
Step 7: Preview Results
Also Read
How does the Switch Statement work in Excel VBA?
The Switch Statement evaluates a series of expressions and returns the value associated with the first true expression. It provides a way to choose one value from several possible values based on the evaluation of an expression.
What is the syntax of the Switch statement in Excel VBA?
The syntax of the switch statement in Excel VBA is as follows:
Switch(exp-1, value-1, [ exp-2, value-2..., [ expr-n, value -n ]])
This syntax consists of pairs of expressions and values, where the expression is evaluated, and if it is true, the corresponding value is returned.
Can Switch Statements be nested inside each other?
No, Excel VBA does not support nesting Switch statements directly inside each other. You can use a combination of switch statements and other control structures to achieve complex branching logic.
Similar Reads
Swift - If Statement Just like other programming languages in Swift language also if statement is used to execute the program based on the evaluation of one or more conditions or in other words if statement is used to run a piece of code only when the given condition is true. There are also known as branch statement. Fo
3 min read
VBA IF Statement VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to use how to use the If statement in Excel VBA. Implementat
8 min read
Swift - Break Statement The break statement is a loop control statement that is used to end the execution of an entire control flow statement immediately when it is encountered. When the break condition is true, the loop stops its iterations, and control returns immediately from the loop to the first statement after the lo
6 min read
Swift - Guard Statement Swift provides a special type of statement referred to as "guard" statement. A guard statement is capable to transfer the flow of control of a program if a certain condition(s) aren't met within the program. Or we can say, if a condition expression evaluates true, then the body of the guard statemen
15 min read
Swift - If-else Statement Just like other programming languages in Swift language also support the if-else statement. In the if-else statement, when the given condition is true then the code present inside the if condition will execute, and when the given condition is false then the code present inside the else condition wil
3 min read
Switch statement in Programming Switch statement in programming enables the execution of different code blocks based on the value of an expression, providing a structured approach to handle multiple cases efficiently. It enhances code readability and simplifies decision-making processes, making it a valuable tool for managing prog
6 min read
Swift - If-else-if Statement In Swift, the if-else if-else condition is used to execute a block of code among multiple options. It gives a choice between more than two alternative conditions as soon as one of the conditions is true, the statement associated with that if is executed. If all conditions are false or not satisfied
3 min read
Swift - Fallthrough Statement Just like other languages in Swift also switch statement is very useful and powerful. It takes a value and then compare it with several possible matching pattern or we can say cases and stops its execution when it successfully found its first matching case statement and then execute the code present
4 min read
Electric Switch An electric switch is an important component in an electrical circuit. An electric switch is a simple device that either breaks the circuit or completes it to stop or start the flow of current. An electric switch is an arrangement to switch the circuit ON and OFF as needed. It is also called a "Key"
14 min read
Switch case in R Switch case statements are a substitute for long if statements that compare a variable to several integral values. Switch case in R is a multiway branch statement. It allows a variable to be tested for equality against a list of values. Switch statement follows the approach of mapping and searching
2 min read