Excel VBA 2010 Lesson 12: Looping

TwitterShare/Bookmark

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

Excel VBA 2010 allows a procedure to repeat 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 2010 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.

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


Example 12.1

In this Excel VBA 2010 programme, you place the command button on the spreadsheet then click on it to go into the Excel VBA 2010 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 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 means there more 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]