In the previous lesson, we have learned how to use If….Then….Else statement. However, for multiple options, the If…Then…Else structure could become too bulky and difficult to debug. Fortunately, Excel 2010 VBA 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 Else End Select
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 2010 VBA editor.
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 Sub
In 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.
Private Sub CommandButton1_Click() Dim mark As Single Dim grade As String mark = InputBox("Enter the mark") Select Case mark Case 0 To 29 grade = "F" Case 30 To 49 grade = "E" Case 50 To 59 grade = "D" Case 60 To 69 grade = "C" Case 70 To 79 grade = "B" Case 80 To 100 grade = "A" Case Else grade = "Error!" End Select MsgBox grade End Sub
We can modify Example 11.2 into a user-defined function for use to create a examination report in an Excel Sheet. You can need to key in the mark and use the function to compute the grade in the adjacent cell. For other row, just need to click the dot at the right bottom of te first cell and drag it to where you want, the grade will be automatically updated.
Start an Excel workbook and insert a module in the Visual Basic Editor, and enter the following code:
Function grade(mark As Integer) As String Select Case mark Case 0 To 29 grade = "F" Case 30 To 49 grade = "E" Case 50 To 59 grade = "D" Case 60 To 69 grade = "C" Case 70 To 79 grade = "B" Case 80 To 100 grade = "A" Case Else grade = "Error!" End Select End Function