PPT Excel: How to use VBA Select Case Statement

Do you have to check multiple criteria in your project? You can use the Select Case statement instead of using a nested if statement in your project. In this article, you learn how to use select case statements in excel VBA with an example.

Suppose you have to report the grade of a student based on their score. You can use the nested If Then Else statement in your script to report student grades, which is tedious to write the script and debug later. It will be easier for you to debug or read the script. if you use the Select Case statement.

1. Type the text Student in cell A5, Marks in cell C4, and Grade in cell D4
2. Fill the cell C5 background with the Blue color, which allows user input – student mark.
3. Fill the cell D5 background with an Orange color. Macro read C5 cell value and return the grade.
4. Add an auto shape, fill the desired color and adjust height and width.
5. Type the text Compute Grade

VBA Script

Sub selectCase()

'declare variables
Dim score As Integer, grade As String

'initialize variable
score = ActiveSheet.Range("C5").Value

'select case statement to read score and return the grade
Select Case score
    Case Is >= 90
        grade = "Grade A+"
    Case Is >= 80
        grade = "Grade A"
    Case Is >= 60
        grade = "Grade B"
    Case Is >= 35
        grade = "Grade C"
    Case Else
        grade = "FAIL"
End Select

ActiveSheet.Range("D5").Value = grade

End Sub

Copy and paste the above script into your VBA editor window

Please follow the below steps to assign the selectCase macro to your auto-shape Compute Grade.

1. Select the auto shape Compute Grade
2. Press the right button on the mouse to open the menu
3. Select Assign Macro… – to popup Assign macro dialog box
4. Select selectCase macro
5. Press OK

You can see the mouse pointer turns to a hand symbol when the mouse hover over the auto-shape Compute Grade

Run Script and check the outputs

You can update the marks in cell C5, run the VBA script, and see the different outputs in cell D5.

Conclusions

You can use either nested if then else statement or select case statement when you need to check multiple criteria in your project.

Let me know in the comment box if need to add more examples or specific criteria.