Excel 2010 VBA Lesson 13: Do…Loop

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

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

In this lesson, you will learn about another looping method know as the Do Loop in Excel 2010 VBA.

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 X*2 and the values are displayed 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 LinkedInShare on Reddit