Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Use Switch Statement in Excel VBA?

  • Last Updated : 09 Nov, 2021

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 TypeComments
mark Integerread cell C5 Value
result Stringwrite 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
Recommended Articles
Page :

Start Your Coding Journey Now!