The For – Next Statement | VBA Iteration Statements

by Sola Bode
in VBA Coding Constructs
March 29, 2021

VBA programs often have repetitive execution paths. The For - Next statement enables such VBA loops. Here, we explore this crucial code construct in-depth.

6 minutes read

In everyday life, we often need to repeat certain actions. For instance, Americans vote for a president every November, every four years. Likewise, your program may need to repeat one or more statements several times. Iteration statements like the For – Next statement make such looping possible in VBA.

Introducing the For – Next Statement

The For – Next statement is a fixed-iteration loop that repeats a set of statements a known number of times. It is the simplest type of VBA loop statement. Also, you set the number of iterations by specifying a value-range of a counter variable.

There are several ways of specifying the counter variable’s value range:

You can hardcode it into the program as a literal number or get it from the user via an input box or dialog box.
Moreover, you can also assign another variable to it. Usually, the program would have computed that variable’s value before starting the loop. Of course, both variables must have compatible data types.

The header image above shows the For – Next statement’s syntax. Moreover, the flowchart below illustrates its logic flow.

Flowchart showing the logic flow of the For – Next statement — Flowchart showing the ***For – Next*** statement’s logic flow

Salient Points on Usage

There are several vital things to note about the For – Next statement:

The For statement checks that the counter variable’s value is valid. That means the value is between (or equal to) the initial and (or) final value. If that’s the case, the program runs the statements in the loop body.
The Next statement adds stepsize to or deducts it from the counter variable’s value. Whether stepsize gets added or deducted depends on its sign (positive or negative). Afterward, the execution point returns to the For statement.
Now the previous steps repeat until the counter variable’s value is no longer valid. That means its value falls outside the range specified in the For statement. When that occurs the loop ends.
The loop also ends when execution encounters an Exit For statement in the loop body. Those are usually placed in Selection statements, e.g. If – Then and Select Case statements.
Once the loop ends, the execution point moves to the line right after the Next statement.
Don’t alter the counter variable’s value in the loop body. Doing so makes reading and debugging the code needlessly more difficult.
You can nest these loops within one another, in fact, VBA allows up to 16 levels of nesting. However, each loop’s counter variable must have a unique name. Also, always include the counter variable in each Next statement in nested loops. Although that is optional, doing so boosts your code’s readability.

The For – Next Statement in Action

The image below lists the last ten winners of the U.S. Open women’s singles championship. They are listed in a Range of Cells on a worksheet (named Sheet1) of a Microsoft Excel workbook.

Including the table headers, the range begins at Cells(3,1) and ends at Cells(14,4) – or Range(“A3:D14”). So, the data table has four (4) columns and twelve (12) rows, including the header row.

U.S. Open Women’s Singles Champions, 2010 – 2020

The sample code below illustrates the For – Next statement’s usage. It identifies the champion in any one of the last 10 years.

Option Explicit

Public Sub ForNextStatement_Example01()
    
    'loops through a range of cells & finds out the woman who won
    '   the US Open in a any year within the last 10 years.
    
    'variable declaration
    Dim int_Year As Integer, str_Winner As String
    Dim int_Row As Integer, int_Column As Integer
    
    'get year of interest from user via InputBox function
    int_Year = Val(InputBox("Enter year of interest, YYYY", _
        "US Open Winner - Women's Singles"))
        
    'validate the user's year of interest
    If int_Year < 2010 Or int_Year > 2020 Then
        MsgBox "Year must be between 2010 & 2020", vbOKOnly + vbCritical, _
            "US Open Winner - Women's Singles"
        Exit Sub
    End If
    
    'set worksheet column that will be looped through
    int_Column = 2  'column 'B' has the years
    
    'loop from Year 2020 (worksheet row 4) to Year 2010 (worksheet row 14)
    For int_Row = 4 To 14 Step 1
        
        'user's year matches current row's year? then display winner & year
        With Application.ThisWorkbook.Worksheets(1)
        
            If int_Year = .Cells(int_Row, int_Column).Value Then
                'years match, now assign winner
                str_Winner = .Cells(int_Row, int_Column + 1).Text
                'display both winner and year
                MsgBox str_Winner & " won the " & CStr(int_Year) & _
                    " US Open.", vbOKOnly, "US Open Winner - Women's Singles"
            End If
            
        End With
        
    Next int_Row
    
End Sub

Option Explicit

Public Sub ForNextStatement_Example01()

'loops through a range of cells & finds out the woman who won

' the US Open in a any year within the last 10 years.

'variable declaration

Dim int_Year As Integer, str_Winner As String

Dim int_Row As Integer, int_Column As Integer

'get year of interest from user via InputBox function

int_Year = Val(InputBox("Enter year of interest, YYYY", _

"US Open Winner - Women's Singles"))

'validate the user's year of interest

If int_Year < 2010 Or int_Year > 2020 Then

MsgBox "Year must be between 2010 & 2020", vbOKOnly + vbCritical, _

"US Open Winner - Women's Singles"

Exit Sub

End If

'set worksheet column that will be looped through

int_Column = 2 'column 'B' has the years

'loop from Year 2020 (worksheet row 4) to Year 2010 (worksheet row 14)

For int_Row = 4 To 14 Step 1

'user's year matches current row's year? then display winner & year

With Application.ThisWorkbook.Worksheets(1)

If int_Year = .Cells(int_Row, int_Column).Value Then

'years match, now assign winner

str_Winner = .Cells(int_Row, int_Column + 1).Text

'display both winner and year

MsgBox str_Winner & " won the " & CStr(int_Year) & _

" US Open.", vbOKOnly, "US Open Winner - Women's Singles"

End If

End With

Next int_Row

End Sub

In brief, the sample code above does the following:

Declares Integer and String variables int_Year and str_Winner (code window line #9). These hold the user’s year of interest and that year’s champion, respectively;
Declares Integer variables int_Row and int_Column (line #10). These track the row and column indices of worksheet cells, respectively;
Collects the user’s year of interest via the Val and InputBox functions (line #13). Also, the line continuation character (underscore, _) splits this across two lines;
Confirms that the user’s year of interest is within the range set in the data table. It does so using the If – Then and Exit Sub statements and the MsgBox function (lines #17-21);
Loops through worksheet column ‘B,’ (i.e., int_Column = 2) using a For – Next statement (lines #24-42). Recall that this column holds the championship years;
In each iteration, it tries to match the user’s year of interest to a year in worksheet column ‘B’. It tests for a match using an If – Then statement (line #32-38). Also, it uses the With block (lines #30-40) and the dot operator to avoid fully qualifying each reference to worksheet cells in the block;
Once it finds a matching year (line #32), it assigns a champion to it (line #34). The champion is the name in the worksheet cell on the same row but on column ‘C,’ (i.e., int_Column + 1). That column holds the championship winners’ names;
Finally, it displays the result via a MsgBox function (line #36). Once again the line continuation character splits this across two lines.