Excel 2010 VBA Lesson 13: Do…Loop

 [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



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]

Excel 2010 VBA Lesson 12: Looping

 [Lesson 11]<<[Table of Contents]>>[Lesson 13]

Excel 2010  VBA  allows a procedure to repeat many times until a condition is met. This is called looping.  There are three kinds of loops,  the For…….Next loop, the While….Wend loop and the Do…Loop .



12.1 One level For…..Next Loop

The one level For….Next Loop event procedure is written as follows:

For counter=startNumber to endNumber (Step increment)
One or more VB statements
Next

*We shall deal with the For………Next loop and the While….Wend loop first and the Do….Loop in the next lesson.

Example 12.1

In this Excel 2010 VBA  program, you place the command button on the spreadsheet then click on it to go into the Excel 2010 VBA editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3……until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents a row and j represent a column. We shall learn more about cells in future lessons.

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
 Cells(i, 1).Value = i
 Cells(i,2).Value=i^2
Next
End Sub

* i^2 means i2




The Output is shown in Figure 12.1

vba2010_fig12.1

Figure 12.1

Example 12.2

For……..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.

The code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10  Step 2
 Cells(i, 1).Value = i
Next
End Sub

The Output

vba2010_fig12.2

Figure 12.2




12.2 Nested For….Next Loops

Nested For…….Next loops mean there more than one For…Next Loops are nested within the first level For…..Next loop. The structure is as follows:

For counter=startNumber1 to endNumber1
For counter=startNumber2 to endNumber2
For counter=startNumber3 to endNumber3
 One or more VB statements
Next
Next
Next

Example 12.3

In this example , we use  nested loop to insert the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ……….until cells(10,5).

The code 

Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
 Cells(i, j).Value = i + j
Next
Next
End Sub

The Output

vba2010_fig12.3

Figure 12.3

12.3  While….Wend  Loop

The structure of a While….Wend Loop is very similar to the Do Loop. it takes the following form:

While condition
 Statements
Wend

The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let’s examine the program listed in example 12.4.

Example 12.4

In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
i = 1
While i <= 15
 Cells(i, 1) = i ^ 2
 i = i + 1
Wend
End Sub

The Output

vba2010_fig12.4

Figure 12.4




 [Lesson 11]<<[Table of Contents]>>[Lesson 13]

Excel VBA Lesson 15: The Do Loop

<<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>>

Excel VBA Lesson 14: Looping

<<Lesson 13>> [Contents] <<Lesson 15>>

Another procedure that involves decision making is looping. Excel VBA allows a procedure to be repeated many times until a condition or a set of conditions is fulfilled. This is generally called looping .  Looping is a very useful feature of  Excel VBA because it makes repetitive works easier. There are  two kinds of loops in Visual Basic,  the For…….Next loop, the While….Wend loop and the the Do…Loop . We shall deal with the For………Next loop  and the While….Wend loop first and we will deal with the Do….Loop in the next lesson.

14.1 One level For…..Next Loop

The one level For….Next Loop event procedure is written as follows:

For counter=startNumber to endNumber (Step increment)

One or more VB statements

Next

Example 14.1

In this Excel VBA program, you place the command button 1 on the spreadsheet then click on it to go into the Visual Basic editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3……until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents row and j represent the column.

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next
End Sub

The Output is shown in Figure 5.1

Excel VBA




Figure 14.1

Example 14.2

For……..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.

The code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10  Step 2
Cells(i, 1).Value = i
Next
End Sub

Excel VBA

The Output

 Figure 14.2

 14.2 Nested For….Next Loops

Nested For…….Next loops mean there are more than one For…Next Loops are nested within the first level For…..Next loop. The structure is as follows:

For counter=startNumber1 to endNumber1
For counter=startNumber2 to endNumber2
For counter=startNumber3 to endNumber3
One or more VB statements
Next
Next
Next

Example 14.3

In this example , we use  nested loop to put the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ……….until cells(10,5). The code and output are shown below.

Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
Cells(i, j).Value = i + j
Next
Next
End Sub

The Output

Excel VBA

Figure 14.3

14.3  While….Wend  Loop

The structure of a While….Wend Loop is very similar to the Do Loop. it takes the following form:

While condition
Statements
Wend

The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let’s examine the program listed in example 14.4.

Example 14.4

In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)

The Code

Private Sub CommandButton1_Click()
Dim i As Integer

i = 1
While i <= 15

Cells(i, 1) = i ^ 2
i = i + 1
Wend

End Sub

The Output

vba_example5.4

Figure 14.4

<<Lesson 13>> [Contents] <<Lesson 15>>