Control Flow Statements: Loops and Goto

For … Next

For counter = start To end [ Step step ]

Actions

[ Exit For ]

Actions

Next [ counter ]

The For Loop requires a counter, usually an integer. Brackets show optional specifications. The program flow begins with the counter set equal to the start value. On the next iteration, the counter is incremented by the Step value step, which defaults to one if left unspecified. Sometimes, the loop will need to stop when certain conditions occur. The [Exit for] construct provides a mechanism for effecting this.

For Each … Next

For Each member In group

Actions

[ Exit For ]

Actions

Next [ member ]

A For Each Loop allows iteration through members of a group, which may be of any data type.

For Each Loop stepping through string collections

In this example, the array of elements str(2) is the group (of dimension 3) and rstr is a variant variable used to step through the group.

Note: VBA array indexing always begins with zero (0) and not one (1). Had we dimensioned str as str(3), then Msgbox would be invoked four times, instead of three, and in the final iteration, it would display a blank since str(3) was never set.

Try this example by first dimensioning str as str(2), then as str(3).

Do … Loop

Do

Counter = Counter + 1

If Counter > Nmax then exit do

Actions

[ Exit Do ]

Actions

Loop

A Do Loop needs at least one specified condition for exit, otherwise an infinite loop occurs. See Infinite Loops and How to Escape Them.

Do Loop with exit criteria

In this example, the counter I is used to help limit the number of iterations the loop is allowed. This is a common safeguard, but does not guarantee exit from the loop because it cannot guarantee that the maximum of 10 will always be reached, since coding inside the loop can interfere with reaching the end goal. Consider the absurd example below.

Do Loop with exit criteria but an infinite loop

In this scenario, I never reaches the maximum of 10, it always stays at the value of one (1), resulting in a program that will continue running until an outside intervention stops it.

Do … Loop Until

Do Until Criteria Met

Actions

[ Exit Do ]

Actions

Loop

Or

Do

Actions

[ Exit Do ]

Actions

Loop Until Criteria Met

This type of Do Loop provides a more compact method of placing a limit on the number of iterations allowed, but for the same reasons as above, cannot guarantee loop exit.

Do Until Loop — two methods

Or

Do While … Loop

Do While Criteria Not Met

Action

[ Exit Do ]

Actions

Loop

Or

Do While

Action

[ Exit Do ]

Actions

Loop While Criteria Not Met

Similar to the Do Until Loop, this type of loop continues as long as the criteria has not been met. The Do Until Loop is equivalent to the Do While Loop, just slightly different ways of stating the exit criteria.

Do While Loop — two methods

Or

While … Wend

While Criteria Not Met

Actions

[ Goto ]

Actions Wend

The While Loop is a more succinct way to construct an iteration. Exiting a While loop early can be done in one of two ways: use a Goto statement or use a Boolean Flag to exit before the outer loop criteria is reached.

While Loop with early exit — Goto or Boolean flag

Or

While Loop with both exit types — Goto and Boolean flag

The two exit mechanisms for While Loops can be used inside the same loop to accommodate multiple scenarios for controlling the number of iterations.

Nested Loops

Loops may be nested meaning that you can construct loops within loops. Nested loops work something like peeling layers of an onion. The outer most loop executes first, then the next closest to the outer loop, and so on. This continues until the outer limit stop criteria is met or some other escape mechanism is triggered.

Nested For Loop using Step and early exit

The For J loop is nested inside the For I loop, so For J is the outer loop and For I the inner loop. If we nest another loop inside the For J loop, say a For K loop, then For K is a inner loop to For J, which in turn is an outer loop to For K.

In this example, the For I loop iterates with I value set to 1, 3, 5, 7, 9, after which this loop is exited. At each value of I, the For J loop steps through J values 1, 2, 3, 4.

Loops involved in nesting can be of different types, so one loop can be a For Loop and another a Do Loop, totally a mix and match. You can also have two or more independent loops nested inside an outer loop. So the inner loops are not nested inside one another, but are all nested inside the outer loop. Nesting loops can become complicated the more levels you use.

Goto statements

Goto N

N Line of code

Or

Goto N

N Comment Line

Goto statements are branching statements that transfer control to a specified line within the routine. The line to which transfer is given begins with the same number in the Goto statement. No two lines can have the same number.

Goto branching

How to Escape from an Infinite Loop

An Infinite Loop is a code configuration in which the program cannot stop running. It most often occurs within a loop of some sort, but can also happen outside loops. Let's start with a simple example.

Infinite loop

This program will continue running indefinitely because there are no exits from this Do Loop. Although VBA is great at warning about improper coding, it will not and cannot alert you to infinite loops.

In a Windows environment, there are three options for stopping program execution:

1. Hold the Esc key down for a few seconds.
2. Depress the Ctr + Break/Pause buttons simultaneously.
3. Bring up the Task Manager using Ctr + Alt + Delete. Click on any instance of Excel, then click the End Task button. This will close all open Excel applications and provide for a recovery file.

The first two options do not always work, but when they do, your Excel application will still be open without loss of data. The third option can lead to loss of data if your work was not saved before running the program. For this reason you should save your work before running new/untested code. Of course, you could always power down and restart your computer, but this is not recommended and should only be done as a last-ditch effort.