13. Control of Macro Flow - Loops
Often we want to repeat some actions performed by a macro, for a certain number of times, for each item in a collection, or until a condition is true/not true (or while a condition is true/untrue). We can do this by placing the commands in a loop. There are three basic types of loops: Do loops, For loops, and For Each loops. Very often any of these types of loops could be used in a specific situation, and the choice depends on which loop is simplest and most convenient to use.

As an example, let us say we want to go through all the rows of a spreadsheet, and if the value in column "A" is over 100, then we want to bold the cell. We will do this three different ways, using each of the different types of loops. (This can in fact be done with conditional formatting in the worksheet, but we will use it as a simple macro example.)

13.1 "Do" Loops

When we want a macro to repeat a set of commands until a condition becomes true, or while a condition is true, we use a Do loop. The form of this loop is Do...Loop Until [condition] / Do...Loop While [condition], or Do Until [condition]...Loop / Do While [condition]...Loop. The difference between the first pair and the second pair is that in the first the loop will always execute at last once, since the condition is not evaluated until the end of the loop, while in the second pair if the condition is not true at the beginning of the loop, the loop is never executed.

For our example, we will assume a blank cell in column "A" marks the last used row on the worksheet:

Cells(1, 1).Activate

Do Until ActiveCell.Value = ""
If ActiveCell.Value > 100 Then
ActiveCell.Font.Bold = True
End If

ActiveCell.Offset(1, 0).Activate
Loop
Instead of using the Offset property to identify the next cell to activate, we could use a variable. Using While instead of Until to ensure that the loop will execute at least once, the code would be:
vRow = 1

Cells(vRow, 1).Activate

Do
If ActiveCell.Value > 100 Then
ActiveCell.Font.Bold = True
End If

vRow = vRow + 1

Cells(vRow, 1).Activate
Loop While ActiveCell.Value <> ""
Notice that we can nest if statements and loops within one another. The indentation of the code for the if statement and loop makes it easier to see the blocks of commands for each one.

BEWARE OF ENDLESS LOOPS! If you leave out the terminating condition, or the terminating condition you specify is never true, then the macro will happily chug along, executing the loop over and over. Depending on what you are doing, you may eventually get an error, you may cause the program or your entire system to crash, or you may just wind up sitting there wondering why nothing seems to be happening since the macro never ends. In any case this is never the outcome you want. Always make sure when coding loops that you have specified a terminating condition, and that at some point that condition will become true.

13.2 "For" Loops

Instead of limiting the number of executions of a loop by a condition, there are times when the number of executions is to be determined by a numerical value. This is done using a For...Next loop. The form of the statement is
For [counter]=[initial value] To [ending value] Step [number]
block of macro commands
Next
The counter is just a variable you specify; it is common to use "i". On the first loop the counter will have the initial value, and each time the loop begins again the counter will be incremented by the number after Step (or decremented, if the Step value is negative). Step is optional; if it is omitted the increment will be 1. The initial and ending values can be anything that evaluates to or returns a number: it can be a number, a variable, an expression such as x + 1 or a function expression, or the Count property of an objects collection. The loop will continue to execute until the counter exceeds the ending value.

To code our example, let us assume that 65 rows are used on the worksheet, and the first row is the heading row. Using a For...Next loop:

For i = 2 To 65
Cells(i, 1).Activate

If ActiveCell.Value > 100 Then
ActiveCell.Font.Bold = True
End If
Next

There are several potential "gotchas" with For loops. Never change the value of the counter variable by a command in your code because doing that could easily cause the loop to behave unexpectedly. If you nest For loops within one another, make sure to use a different counter variable for each For loop.

Additionally, if you are doing deletions of members of a collection using a For loop, you will probably need to count backwards, by using Step -1. Suppose you are processing each row on a worksheet containing 1,550 rows, using For vRow = 1 To 1550 to delete rows that meet a certain condition. You use the counter variable to specify the row number to process on each iteration through the loop. Rows 1 through 4 do not meet the condition, and are not deleted. On the 5th loop, row 5 does meet the condition, and is deleted. When row 5 is deleted, the old row 6 becomes row 5. But your next row number to process using the counter variable is 6, so the old row 6 (which is now row 5) is skipped, and never processed! To avoid this, you need to use For i = 1550 To 1 Step -1.

13.3 "For Each" Loops

To iterate through all the objects in a collection, the For Each [object variable] In [collection]...Next loop is used. On each loop, the next object in the collection is assigned to the object variable.

In our example, we don't need to check the unused portion of the worksheet, but we don't want to have to know what the last row is. We can use the UsedRange property of Worksheet, which returns a Range object representing the area used on a worksheet. A Range object has a property named Cells, representing all the cells in the range. Coding our example using a For Each loop:

For Each vCell in ActiveSheet.UsedRange.Cells
If vCell.Value > 100 Then
vCell.Font.Bold = True
End If
Next vCell
In the code above, the variable vCell refers to each member of the collection in turn; in other words, to each cell in UsedRange. Because it refers to an object, the variable effectively has the same members as the object itself; the variable just stands for the object. In fact, if you declare the object variable used in the For Each statement, the VBA editor will recognize it and will use the autocomplete feature to show the applicable members of the object. You can name the object variable anything you like, but it is often helpful to use a variation of the name of the object itself to help make your code more readable.

Notice that in this code we included the variable in the Next statement. This can be done optionally for any Next statement and doing so can make your code more readable, especially if you have nested loops.

Also, you see that we did not activate the cell. There is no need to do so, since a macro can set the properties of a cell (or any object) without activating it. Eliminating activation (cursor movement) speeds up macro execution, as discussed below.

13.4 Premature Termination

Normally the reptitions of a loop end based on the ending condition specified in the loop statement. Normally a macro ends when the last statement in the macro has been executed. Often, however, we want to end a loop and proceed to the next statement, or end the entire macro, before that. VBA provides several statements to do that:
Exit Do ends a Do loop.
Exit For ends a For...Next or For Each...Next loop.
Once the applicable Exit statement is executed, execution of the macro resumes with the statement immediately following the loop.
The End statement causes macro execution to stop at that point; no further commands of the macro are executed.
As an example, if we want to limit the process in the above For Each loop to the first 500 rows, we could code that using an Exit For statement:
For Each vCell in ActiveSheet.UsedRange.Cells
If vCell.Value > 100 Then
vCell.Font.Bold = True
End If
If vCell.Row > 500 Then Exit For
Next vCell

13.5 Speeding Up Macro Execution

Most macro commands execute extremely quickly; short and simple macros are done before you know it. Longer and more complicated macros may seem to execute more slowly, but in fact it is often not the macro code execution that takes time, but the repainting of the screen, i.e., the change in the display due to selection and/or cursor movement. It is seldom necessary to see the screen repaint (usually this is needed only when debugging a macro). You can speed up the macro by eliminating the screen repainting, so that the display does not change until the macro ends.

To turn off screen repainting, use the command Application.ScreenUpdating = False. Remember to turn it back on with Application.ScreenUpdating = True before the macro ends.

Even if screen updating has been turned off, if you activate or select ranges in your macro those actions can slow down execution. The methods of ranges can be called, and the properties of ranges can be set, without activating or selecting them, and it usually faster (and simpler) to do so.