How to Use Switch Statement in Excel VBA?

We can use Select case instead of using multiple if then statements in Excel VBA. In the below example user will enter the student mark in “Cell C5” as an input.  Macro read C5 cell value and fill grade in “Cell D5”.

Grade:

• <35 – Fail
• 35-60 – Grade C
• 60-80 – Grade B
• >=80 – Grade A

VBA Code read cell C5 value, use select case statement (apply criteria) and fill grade of a student.

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

Step 1: Just create a template like below

Step 2: Add a shape “Update Grade”

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

Step 4: Select “updateGrade”, you can see a list of available macros in your workbook

Step 5: Save your excel file as “Excel Macro-Enabled Workbook” *.xlsm

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

Outputs:

My Personal Notes arrow_drop_up

Save