Excel VBA 2010 Lesson 11: Decision Making 2: Select Case…..End Select

TwitterShare/Bookmark

 [Lesson 10][Table of Contents][Lesson 12]

In previous lesson, we have learned how to write Excel VBA 2010 code using If….Then….Else statement. However, for multiple options, the If…Then…Else structure could become too bulky and difficult to debug. Fortunately, Excel VBA 2010 provides another way to handle complex multiple choice cases, that is, the Select Case…End Select decision structure. The structure of Select Case…End Select is as follows:

Select Case variable
Case value 1
Statement
Case value 2
Statement
Case value 3
Statement
.
.
.
.
Case ElseEnd Select


We shall demonstrate the use of Select Case…End Select in the following examples:

Example 11.1

In this example, we want to automatically assign a remark in relation to a certain examination grade. For example, if the grade is A, we shall assign remark as High distinction, A- as distinction and so forth. To enter the code, start Excel 2010 then place a command button onto the spreadsheet and click on it to launch the Excel VBA 2010 editor.

The code:

Private Sub CommandButton1_Click()
Dim grade As String
Dim grade As String
grade = InputBox(“Enter the grade(A, A-, B, C, E or F”)
Select Case grade
Case “A”
MsgBox “High Distinction”Case “A-”
MsgBox “Distinction”

Case “B”
MsgBox “Credit”

Case “C”
MsgBox “Pass”

Case Else
MsgBox “Fail”

End Select

End Select
End Sub


Example 11.2

In the this  example, we shall  show you how to process the grades of students according to the marks given. For example, if a student’s mark is 85, the corresponding grade is A and if the mark is 20 the grade will be F and so forth. We can use the statement case value1 to value 2  to specify the range of values that fulfill the particular case.

You should also include the error case where the values entered are out of the range or invalid. For example, if the examination mark is from 0 to 100, then any value out of this range is invalid.

The code

Private Sub CommandButton1_Click()

Dim mark As Single
Dim grade As String
mark = InputBox(“Enter the mark”)

Select Case mark

Case 0 To 20
grade = “F”

Case 20 To 29
grade = “E”

Case 30 To 39
grade = “D”

Case 40 To 59
grade = “C”

Case 60 To 79
grade = “B”

Case 80 To 100
grade = “A”

Case Else
grade = “Error!”

End Select

MsgBox grade

End Sub




 [Lesson 10][Table of Contents][Lesson 12]