Using For...Next

You can use For...Next statements to run a block of statements a specific number of times. For loops, use a counter variable whose value increases or decreases with each repetition of the loop.

The following example causes a procedure called MyProc to execute 50 times. The For statement specifies the counter variable x and its start and end values. The Next statement increments the counter variable by 1.

The following examples illustrates the use of the for next loop:

Example 1:

Sub DoMyProc50Times()
   Dim x
   For x = 1 To 50
      'This loop executes 50 times
   Next
End Sub

Using the Step keyword, you can increase or decrease the counter variable by the value you specify. In the following example, the counter variable j is incremented by 2 each time the loop repeats. When the loop is finished, the total is the sum of 2, 4, 6, 8, and 10.

Example 2:

Sub TwosTotal()
Dim j, total
    total = 0
    For j = 2 To 10 Step 2
        total = total + j
    Next
 'The total is 30
End Sub

To decrease the counter variable, use a negative Step value. You must specify an end value that is less than the start value. In the following example, the counter variable myNum is decreased by 2 each time the loop repeats. When the loop is finished, total is the sum of 16, 14, 12, 10, 8, 6, 4, and 2.

Example 3:

Sub NewTotal()
   Dim myNum, total
    total = 0
    For myNum = 16 To 2 Step -2
        total = total + myNum
    Next
    'The total is 72
End Sub

You can exit any For...Next statement before the counter reaches its end value by using the Exit For statement. Because you usually want to exit only in certain situations, such as when an error occurs, you should use the Exit For statement in the True statement block of an If...Then...Else statement. If the condition is False, the loop runs as usual.

Example 4:

Sub ExitForExample()
    	Dim i, total
    	total = 0
    	For i = 1 To 10
        		total = total + i
       		 If i = 5 Then Exit For
    	Next
    	'Exited loop early. Total is 15
End Sub

Example 5:

Sub DecreaseWithExit()
    	Dim n, sum
    	sum = 0
    	For n = 20 To 0 Step -2
        		sum = sum + n
        		If n = 10 Then Exit For
    	Next
   	 'Exited loop early. sum is 90
End Sub