Using For...Next

Using For…Next

Sometimes it's convenient to execute a set of statements more than once. That's why VBA includes looping statements. The first looping statement you should know about is For…Next.

To see why such statements are useful, let's start with this example, which does not include any looping statements:

 Sub PrintWeek(dtmStart As Date)   ' Print out a week's worth of dates   ' starting at dtmStart   Debug.Print dtmStart   Debug.Print DateAdd("d", 1, dtmStart)   Debug.Print DateAdd("d", 2, dtmStart)   Debug.Print DateAdd("d", 3, dtmStart)   Debug.Print DateAdd("d", 4, dtmStart)   Debug.Print DateAdd("d", 5, dtmStart)   Debug.Print DateAdd("d", 6, dtmStart) End Sub 

Figure 6.1 shows this procedure in action. As you can see, it prints a set of seven dates starting at a specific date to the Immediate window. In real life, you'd probably store this information somewhere, or print it to a printer, but you don't yet have the VBA tools to perform these tasks.

Figure 6.1. Printing dates without a loop.

Although this procedure works as advertised, there are two problems with it. First, the code is very repetitive; it takes a lot of code to do such a simple task. Second, it's inflexible. What if you want to print three dates, or 30? You have to actually change the code to do so.

You can overcome both of these problems with the For…Next looping structure. First, here's the original procedure converted into a loop:

 Sub PrintWeek(dtmStart As Date)   ' Print out a week's worth of dates   ' starting at dtmStart   Dim intI As Integer   For intI = 0 To 6     Debug.Print DateAdd("d", intI, dtmStart)   Next intI End Sub 

In this procedure, the variable intI is called the loop counter. The For…Next structure assigns the values from 0 through 6 to this variable and executes the statements between the For and the Next statements once for each of those values. Here there's just a single statement in the body of the loop, but you can have more than one statement within the loop.

NOTE

Traditionally, loop counters are named with short variable names such as i, j, and k. If you're using a naming convention, you might prefer (as I do) intI, intJ, and intK. Some people use a meaningful counter name (such as intDays) instead.

TIP

You can omit the loop variable from the Next statement, simply writing Next instead of Next intI. I prefer to include the variable, but don't get confused if you see code without it.

Looping in Reverse

The first loop that you saw uses the values 0, 1, 2, 3, 4, 5, and 6 in that order for the loop counter. The default behavior of the For…Next loop is to increment the loop counter by 1 each time that it executes the loop. But you can actually change the loop counter by a different value. Here's a version that counts backward:

 Sub PrintWeekReverse(dtmStart As Date)   ' Print out a week's worth of dates   ' ending at dtmStart   Dim intI As Integer   For intI = 6 To 0 Step -1     Debug.Print DateAdd("d", intI, dtmStart)   Next intI End Sub 

The Step part of the For statement tells VBA to (in this case) subtract 1 from the loop counter each time it executes the loop. You can use Step to change the loop in other ways as well. For example, to count by threes, you set up your loop this way:

 For intI = 0 to 9 Step 3   statements Next intI 

In this case, the statements in the loop are executed four times, with intI having the values 0, 3, 6, and 9.

Using a Variable for the Loop Counter

In the second version of the PrintWeek procedure, the loop counter ranges over a fixed set of values from 0 to 6, but those values can be variables as well. Here's a more flexible procedure:

 Sub PrintDays(dtmStart As Date, intDays As Integer)   ' Print out a week's worth of dates   ' starting at dtmStart   Dim intI As Integer   For intI = 0 To intDays - 1     Debug.Print DateAdd("d", intI, dtmStart)   Next intI End Sub 

Now the number of days to print is controlled by the second argument to the procedure. Figure 6.2 shows how you might call this version to print four successive dates.

Figure 6.2. Printing dates with a loop.

Nesting For…Next Loops

Loops can also contain other loops. Nesting loops let you write even more condensed code under some circumstances. Suppose you want to print timecards with slots for every hour during the work day across a week. You can do this by using one loop for the days and another inner loop for the hours:

 Sub PrintTimecard(dtmStart As Date)   ' Print out hourly timecard for a week   Dim intI As Integer   Dim intJ As Integer   For intI = 0 To 6     Debug.Print DateAdd("d", intI, dtmStart)     For intJ = 0 To 8       Debug.Print "  " & DateAdd("h", intJ, #9:00:00 AM#)     Next intJ   Next intI End Sub 

A little thought will show you how this works. The outer loop, using intI as the loop variable, walks through the seven days of the week. For each value of intI, it prints the day, and then calls the inner loop. The inner loop is executed nine times before control returns to the outer loop. In case you're having trouble visualizing the results, Figure 6.3 shows how this works.

Figure 6.3. Using nested loops.

This procedure prints 70 lines of output. By using nested loops, it achieves this task with 8 lines of code rather than 70 Debug.Print statements.

TIP

You're not limited to nesting two loops; you can nest three, four, five, or even more loops if you've got a good use for such code.

Aborting a For…Next Loop

Sometimes you might change your mind and not want to complete all the cycles through a For…Next loop. For those times, the VBA language contains the Exit For statement. Consider this procedure, which returns the date of the Monday that most closely follows the given date:

 Function GetNextMonday(dtmDateStart As Date) As Date   ' Returns the date of the next Monday   Dim intI As Integer   Dim dtmDayToCheck As Date   For intI = 1 To 7     dtmDayToCheck = DateAdd("d", intI, dtmDateStart)     If DatePart("w", dtmDayToCheck) = 2 Then       GetNextMonday = dtmDayToCheck       Exit For     End If   Next intI End Function 

It's obvious that there is always a Monday no more than seven days after the given date. The function uses a For…Next loop to create each of those seven dates, using the DateAdd function. Each time, it determines whether the date is a Monday. If it is, that date can be returned, and there's no point in completing the For…Next loop. The Exit For statement tells VBA to move to the next statement after the entire For loop and to continue execution there; in this case, that's the End Function statement.

A second form of loop, the For…Each loop, is used with object variables. For information on For…Each loops, see "Working with Collections," p. 119.