For Next loops

The simplest type of loop is a For-Next loop. Here's the syntax for this structure:

For counter = start To end [Step stepval] [statements] [Exit For] [statements] Next [counter]

The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how this works, keep reading.

A For-Next example

The following example shows a For-Next loop that doesn't use the optional Step value or the optional Exit For statement. This routine loops 100 times and uses the VBA Rnd function to enter a random number into 100 cells:

 Sub FillRange() Dim Count As Integer For Count = 1 To 100 ActiveCell.Offset(Count - ■ 1, 0) = Rnd Next Count End Sub

In this example, Count (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because I didn't specify a Step value, VBA uses the default value (1). The Offset method uses the value of

Count as an argument. The first time through the loop, the procedure enters a number into the active cell offset by zero rows. The second time through (Count = 2), the procedure enters a number into the active cell offset by one row (Count -1), and so on.

i Because the loop counter is a normal variable, you can change its value within the block of code between the For and the Next statements. This, however, is a very bad practice. Changing the counter within the loop can have unpredictable results. Take special precautions to ensure that your code does not directly change the value of the loop counter.

A For-Next example with a Step

You can use a Step value to skip some values in a For-Next loop. Here's the same procedure as in the preceding section, rewritten to insert random numbers into every other cell:

 Sub FillRange() Dim Count As Integer For Count = 1 To 100 Step 2 ActiveCell.Offset(Count - 1, 0) = Rnd Next Count End Sub

This time, Count starts out as 1 and then takes on a value of 3, 5, 7, and so on. The final Count value is 99. The Step value determines how the counter is incremented.

This chapter introduces looping via the BadLoop example, which uses a GoTo statement. Here's the same example, which is available on this book's Web site, converted into a good loop by using the For-Next structure:

 Sub FillRange() Dim StartVal As Integer Dim NumToFill As Long Dim CellCount As Long StartVal = CInt(InputBox("Enter the starting value: ")) NumToFill = CInt(InputBox("How many cells? ")) For CellCount = 1 To NumToFill ActiveCell.Offset(CellCount - 1, . 0) = _ StartVal + CellCount - 1 Next CellCount End Sub

A For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately.

The following example, available on the book's Web site, demonstrates the Exit For statement. This routine identifies which of the active worksheet's cells in column A has the largest value:

Sub ExitForDemo()

Dim MaxVal As Double Dim Row As Long

MaxVal = Application.WorksheetFunction. _

If Range("A1").Offset(Row - 1, 0).Value = MaxVal Then Range("A1").Offset(Row - 1, 0).Activate MsgBox "Max value is in Row " & Row Exit For End If Next Row End Sub

The routine calculates the maximum value in the column by using Excel's MAX function and assigns the result to the MaxVal variable. The For-Next loop then checks each cell in the column. If the cell being checked is equal to MaxVal, the routine doesn't need to continue looping (its job is finished), so the Exit For statement terminates the loop. Before terminating the loop, the procedure activates the cell with the maximum value and informs the user of its location.

A nested For-Next example

So far, all this chapter's examples use relatively simple loops. However, you can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.

The following example uses a nested For-Next loop to insert random numbers into a 12-row-by-5-column range of cells, as shown in Figure 10-2. Notice that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) = Rnd statement 60 times.

Sub

FillRange2()

Dim Col As Integer

Dim Row As Long

For Col = 1 To 5

For Row = 1 To

12

Cells(Row,

Col) = Rnd

Next Row

Next Col

End

Sub

These cells were filled using a nested For-Next loop.

Figure 10-2:

These cells were filled using a nested For-Next loop. The next example uses nested For-Next loops to initialize a three-dimensional array with zeros. This routine executes the statement in the middle of all the loops (the assignment statement) 1,000 times, each time with a different combination of values for i, j, and k:

 Sub NestedLoops() Dim MyArray(10, 10, 10) Dim i As Integer Dim j As Integer Dim k As Integer For i = 1 To 10 For j = 1 To 10 For k = 1 To 10 MyArray(i, j, k) = 0 Next k Next j Next i End Sub

Refer to Chapter 7 for information about arrays.

Refer to Chapter 7 for information about arrays.

0 0