The For Each – Next Statement | VBA Iteration Statements

In everyday life, we often need to repeat certain actions. For instance, the Brits celebrate St. George’s Day every April 23^rd. Likewise, your program may need to repeat one or more statements several times. Iteration statements like the For Each – Next statement make such looping possible in VBA.

Introducing the For Each – Next Statement

The For Each – Next statement is a fixed-iteration loop that repeats a set of statements a known number of times. That fixed number of iterations is equal to the number of objects in a collection, or elements in an array.

So, the For Each – Next statement runs the statements in its loop body once for each member found in a group. That group may be a collection or array. Moreover, each object or element in the group is called a member-variable.

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

Salient Points on Usage

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

• The For Each statement first checks that there is at least one member in the group. That means affirming that a collection’s Count property or an array’s length exceeds zero. If so, the program assigns the group’s first member to the member-variable. Then the program runs the statements in the loop body once.
• The Next statement assigns the group’s next member to the member-variable. It then returns the execution point to the For Each statement.
• Now the previous steps repeat until, at some point, there is no next member. That means the loop has run once for all the group’s members, so 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.
• Only variant or object variables are valid member-variables for collections. Whereas, only variant variables are valid member-variables for arrays. So, an array of user-defined types can’t be the group in a For Each – Next statement. That’s because variants can’t handle user-defined types.
• You can nest these loops within one another, in fact, VBA allows up to 16 levels of nesting. However, each loop’s member-variable must have a unique name. Also, always include the member-variable in each Next statement in nested loops. Although that is optional, doing so boosts your code’s readability.

The For Each – 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.

The sample code below illustrates the For Each – Next statement’s usage. It finds out how many times a player has won the champion in any one of the last 10 years.

In brief, the sample code above does the following:

• Declares String and Byte variables str_Winner and byt_Count (code window line #9). These hold the name of the player that interests the user and their title count, respectively;
• Declares Range object variables rng_Winner and rng_Winners (line #10). These variables reference an individual cell and a collection of cells, respectively;
• Collects the user’s player of interest using the Show and Hide methods of a UserForm (lines #13-16). It also uses the Text property of the form’s sole ListBox control in the data collection (line #15). Moreover, it uses the With block and the dot operator to avoid fully qualifying each form control reference in the block. The image and sample code below show the UserForm and its code;

• Defines the collection of cells (i.e., a Range) to loop through (line #19). That would be the winners’ list in the earlier image, so from worksheet Cells(4,3) to Cells(14,3). Also, it uses the Set statement to assign this collection of cells to rng_Winners;
• Loops through the collection, rng_Winners, and matches the user’s player of interest, str_Winner, to each instance of that player in the collection, rng_Winner (lines #22-27). It uses an If–Then statement for the match decision (line #23). Each time it finds a player match, it increases the match frequency, byt_Count, by a value of ‘1’ (line #25);
• Finally, it displays the result via a MsgBox function (lines #30-32). It does that after having looped through the entire collection. Also, a line continuation character (underscore, _) splits the MsgBox function across three lines.