Excel VBA Lesson 15: The Do Loop

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit

<<Lesson 14>> [Contents] <<Lesson 16>>

In the previous lesson, you have learned how to use the   For……..Next loop and the While…..Wend loop to execute a repetitive process. In this lesson,  you will learn about another looping method known as the Do Loop. There are four ways you can use the Do Loop, as shown below:

(i) Do………..Loop While

(i) Do………..Loop While

(ii) Do until………….Loop

(iii) Do while…………Loop

(iv) Do…………Loop until




We shall illustrate the four structures of Do Loops in the following examples:

Example 15.1

Arranging numbers in ascending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = counter
Loop While counter < 10

End Sub

In this example, the program will keep on adding 1 to the preceding counter value as long as the counter value is less than 10. It displays 1 in cells(1,1), 2 in cells(2,1)….. until 10 in cells (10,1).

The Output

Excel VBA

Figure 15.1

Example 15.2

Arranging numbers in descending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do Until counter = 10
counter = counter + 1
Cells(counter, 1) = 11 – counter
Loop
End Sub

In this example, the program will keep on adding 1 to the preceding counter value until the counter value reaches 10. It displays 10 in cells(1,1), 9  in cells(2,1)….. until 1 in cells (10,1).

The Output

Excel VBA

Figure 15.2

Example 15.3

Private Sub CommandButton1_Click()
Dim counter , sum As Integer

‘To set the alignment to center
Range(“A1:C11”).Select
With Selection
.HorizontalAlignment = xlCenter
End With

Cells(1, 1) = “X”
Cells(1, 2) = “Y”
Cells(1, 3) = “X+Y”

Do While counter < 10
counter = counter + 1
Cells(counter + 1, 1) = counter
Cells(counter + 1, 2) = counter * 2
sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)
Cells(counter + 1, 3) = sum
Loop
End Sub

In this example, the program will display the values of X in cells(1,1) to cells(11,1). The value of Y is X2and the values are display in column 2, i.e. from cells(2,1) to cells(2,11). Finally, it shows the values of X+Y in column 3, i.e. from cells(3,1) to cells(3,11)

The Output

Excel VBA
Figure 15.3

<<Lesson 14>> [Contents] <<Lesson 16>>

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit