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”.
- <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.
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 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