Excel VBA 2010 Lesson 13: Do…Loop

 [Lesson 12][Table of Contents][Lesson 14]

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 Excel VBA 2010. In this lesson, you will learn about another looping method know as the Do Loop.

There are four ways you can use the Do…Loop, as shown below:

(i) Do…Loop While

(ii) Do until…Loop

(iii) Do while…Loop

(iv) Do…Loop until


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

Example 13.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

vba2010_fig13.1

 

Figure 13.1

Example 13.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).

vba2010_fig13.2




Example 13.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

vba2010_fig13.4

 

 

 [Lesson 12][Table of Contents][Lesson 14]

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn