Open In App

VBA Switch Statement

Improve
Improve
Like Article
Like
Save
Share
Report

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

Variable  Data Type Comments
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

Template

Step 2: Add the Update Grade Shape

Update Grade

Step 3: Right-click on Update Grade and Assign Macro

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

Macro name

Step 5: Save your Excel File

Save your Excel file as “Excel Macro-Enabled Workbook” *.xlsm

Save your Excel

Step 6: Click Update Grade

Click “Update Grade” to execute the VBA code and see the output in cell D5.

Execute the VBA

Step 7: Preview Results

Output-1Output-2Output-3Output-4

Also Read

FAQs

What is the switch statement in Excel VBA?

The Switch Statement in Excel VAB is a control structure that evaluates a list of expressions to determine which one is true and then returns a corresponding value associated with that expression.

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.



Last Updated : 19 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads