VBA

Normally it is sufficient to use the conditional statement If....Then....Else for multiple options or selections programs. However, if there are too many different cases, the If...Then...Else structure could become too bulky and difficult to debug if problems arise. Fortunately, Visual Basic provides another way to handle complex multiple choice cases, that is, the Select Case.....End Case decision structure. The general format of a Select Case...End Case structure is as follow:

Select Case   variable

Case value 1

     Statement

Case value 2

     Statement

Case value 3

     Statement

.

.

.

.

Case Else

　

End Select

In the following example, I will show you how to process the grades of students according to the marks given.

Private Sub CommandButton1_Click()

Dim mark As Single
Dim grade As String
mark = Cells(1, 1).Value
'To set the alignment to center
Range("A1:B1").Select
With Selection
.HorizontalAlignment = xlCenter
End With

Select Case mark
Case 0 To 20
grade = "F"
Cells(1, 2) = grade
Case 20 To 29
grade = "E"
Cells(1, 2) = grade
Case 30 To 39
grade = "D"
Cells(1, 2) = grade
Case 40 To 59
grade = "C"
Cells(1, 2) = grade
Case 60 To 79
grade = "B"
Cells(1, 2) = grade
Case 80 To 100
grade = "A"
Cells(1, 2) = grade
Case Else
grade = "Error!"
Cells(1, 2) = grade
End Select

End Sub
　

Explanation:

To set the cell align alignment to center, we use the following procedure:

Range("A1:B1").Select
With Selection
.HorizontalAlignment = xlCenter
End With

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. In this program, I use case else to handle the error entries.

The diagram on the lower left illustrates the output of this example.

VBA VI- Select Case.........End Case