VBA For...Next

---

The For statement ending with Next is used to repeat a code located between these two statements for a given number of times.

Code repetition instructions are also called loop.

Instead of writing like this:

Sub ForNext1()
    i = 1
    MsgBox "Repetition of the code for N. " & i
    i = i + 1
    MsgBox "Repetition of the code for N. " & i
    i = i + 1
    MsgBox "Repetition of the code for N. " & i
    'The Msgbox will pop-up 3 times
End Sub

We can write like this:

Sub ForNext1()
    For i = 1 To 3 'i starts at 1 and goes up to 3
        Msgbox "Repetition of the code for N. " & i
    Next i
    'The Msgbox will pop-up 3 times
End Sub

The For Next statement needs a counter, in this case we used i, which starts at 1. At each command Next i the value of i will increase one unit. The last For repetition will be performed when i equals 3.

The variable used as a counter is chosen by the user and preferably has been previously declared.

---

VBA For Next Step

By default, for every Next i statement in the examples above, a value of +1 is added to the value of i, as in i=i+1.

We can customize this increment with the Step argument.

Sub ForNext1()
    For rep = 10 To 14 Step 2
        Msgbox "Hello VBA World!"
        Msgbox " Repetition " & rep & "."
    Next rep
End Sub

In this example, "Hello VBA World!" was repeated 3 times, starting with rep as 10 and adding plus 2 at each iteration until its value reaches 14, the last iteration. 10, 12 and 14.

In this case, the amount of iterations can be calculated by $({14-10 \over 2}+1)=3$.

Iteration is the name given to one of the many repetitions that a loop can have.

It is possible to assign negative values to Step.

---

• Previous page
• Next page

---

• report error/feedback