In this blog post we will introduce the For…Next Loop and provide some examples of its usage.

The standard syntax of a For…Next loop is:

For counter = start To end
   ...
next i

In the code above counter is a variable that keeps track of the number of loops performed. start is the starting value of the counter and end is the ending value.

The Classic For Loop

Let’s see what a For loop does.

Sub forNext1()
    Dim i As Integer
  
    For i = 1 To 10
    'This loop will increment i by
    'one until it reaches 10
        Debug.Print i
    Next i
End Sub

The output to the immediate window will be:

1
2
3
4
5
6
7
8
9
10

The code block contains a Debug.Print statement which prints the value of i. The For statement increments i by 1 on each iteration and stops when i gets to 10.

Although i increments by 1, we can change the way it increments.

Sub forLoop2()
    Dim i As Integer
 
    For i = 1 To 10
        Debug.Print i
        i = i + 1
        'Because of the above statement
        'this loop will increment by 2
    Next i
End Sub
1
3
5
7
9

Here forLoop2 executes the code block but adds an extra 1 on each iteration.

What happens if we start the For loop at 10 instead of 1?

Sub forLoop3()
    Dim i As Integer
    
    For i = 10 To 1
    'Starting i at 10 means that this
    'loop will not print anything out
    'as it (by default) increments and
    'there is nothing after 10
        Debug.Print i
    Next i
End Sub

Well, nothing actually. The for loop moves forward by default and as 10 is the maximum number in the range, it has nowhere else to go!

Although we are incrementing i, we are also able to increment other variables inside the loop.

Sub forLoop4()
    Dim i As Integer
    Dim t As Integer

    t=0
                                
    For i = 1 To 10
        Debug.Print t
        t = t + 3
        'Although we are incrementing the
        'i variable, we are printing out
        'the value associated with the t
        'variable
    Next i
End Sub
0
3
6
9
12
15
18
21
24
27

In the code below, we demonstrate that the end value of the For loop (5+5) can be an expression.

Sub forLoop5()
    Dim i As Integer
    Dim t As Integer
    
    For i = 1 To 5 + 5
    'Here we are using an expression (5+5)
    'rather than simply using the number 10
        Debug.Print i
    Next i
End Sub

For…Step…Next

In forLoop2 we adjusted the counter i to increment by an additional 1 for each loop. We can do the same by using the Step option in the For loop

Step tells the For Loop to increment its counter by a value other than the default value of 1.

Sub forLoop6()
    Dim i As Integer
    
    For i = 1 To 10 Step 2
    'We are using the Step command
    'to increment i by 2 on each
    'iteration
        Debug.Print i
    Next i
End Sub
1
3
5
7
9

Using Step to Count Backwards

We can go backwards through a loop by using Step – 1 in the For Loop.

Sub forLoop7()
    Dim i As Integer
    
    For i = 10 To 1 Step -1
    'This is how you go backwards through
    'a for loop : Step -1
        Debug.Print i
    Next i
End Sub
10
9
8
7
6
5
4
3
2
1

Using Dynamic startValue, endValue and stepValues

In the below code, startValue, endValue and stepValue are all expressions, so as long as the expressions evaluate to a number, the For Loop will accept them. Here we start at 4, step by 3 and finish at 16.

Sub forLoop8()
    Dim startValue As Integer
    Dim endValue As Integer
    Dim stepValue As Integer
    Dim i As Integer
    
    startValue = 4
    endValue = 16
    stepValue = 3
    
    For i = startValue To endValue Step stepValue
    'Each part of the for expression now contains
    'a variable
        Debug.Print i
    Next i

End Sub
4
7
10
13
16

The For Next Loop is essential learning, if you wish to learn VBA in Access.

Related Posts

Loops – For Each
DoEvents
Looping Through a Recordset
Loops – Do Until / Do While / Do
Loops – While Wend
Nested Loops
What are Recordsets

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube