VBA

The potential of VBA is so great that the sky is the limit. If we sit down and really think hard, we can produce any kind of VBA program. Today was a humid day so I was feeling a bit tired and can't come out with any idea for VB Today. So I went for an afternoon nap. When I woke up, my mind was clear and fresh. Then the idea of a simple VBA counter program suddenly came into my mind. I want to make the counter program able to count the number of passes and the number of fails for a list of marks obtained by the students in an examination. The program also differentiate the passes an fails with blue and red colors respectively. Let examine the codes below:

Private Sub CommandButton1_Click()
Dim i, counter As Integer
For i = 1 To 20

If Cells(i, 2).Value > 50 Then
counter = counter + 1
Cells(i, 2).Font.ColorIndex = 5
Else
'do nothing
Cells(i, 2).Font.ColorIndex = 3

End If
Next i
Cells(21, 2).Value = counter
Cells(22, 2).Value = 20 - counter

End Sub
　

Explanation:
This program combines For..Next and If ...Then...Else statements to control the program flow. If the value in that cell is more than 50, the value of counter is increased by 1 and the font color is changed to blue (the colorIndex is 5) , otherwise there is no increment in the counter and the font color is changed to red (ColorIndex=3)

　
　
　

VBA VIII- Counter