VBA Programming Series...40

Knocking Your Code for a Loop

The term looping refers to repeating a block of VBA statements numerous times. In this section, I explain about several different types of loops.

There are two types of loops: good loops and bad loops. (Good loops get rewarded, and bad loops get sent to their room.)

The following code demonstrates a bad loop. The procedure simply enters consecutive numbers into a range. It starts by prompting the user for two values: a starting value and the total number of cells to fill. (Because InputBox returns a string, I convert the strings to integers by using the CInt function.) This loop uses the GoTo statement to control the flow. The CellCount variable keeps track of how many cells are filled. If this value is less than the number requested by the user, program control loops back to DoAnother.

Sub BadLoop()

Dim StartVal As Long

Dim NumToFill As Long

Dim CellCount As Long

StartVal = InputBox(“Enter the starting value: “)

NumToFill = InputBox(“How many cells? “)

ActiveCell = StartVal

CellCount = 1

DoAnother:

ActiveCell.Offset(CellCount, 0) = StartVal + CellCount

CellCount = CellCount + 1

If CellCount < NumToFill Then GoTo DoAnother _

Else Exit Sub

End Sub

This routine works as intended, but I’m not particularly proud of it. So why is it an example of bad looping? Avoid using a GoTo statement unless it’s absolutely necessary. Using GoTo statements to perform looping

✓ Is contrary to the concept of structured programming. 

✓ Makes the code more difficult to read.

✓ Is more prone to errors than using structured looping procedures.

VBA has enough structured looping commands that you almost never have to rely on GoTo statements for your decision-making. Again, the exception is for error handling.

Now you can move on to a discussion of good looping structures.

For-Next loops

The simplest type of loop is a For-Next loop. Here’s the syntax for this structure:

For counter = start To end [Step stepval] [statements]

[Exit For]

[statements]

Next [counter]

The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the

Next statement are the statements that get repeated in the loop. To see how this works, keep reading.

A For-Next example

The following example shows a For-Next loop that doesn’t use the optional Step value or the optional Exit For statement. This routine loops 20 times and uses the VBA Rnd function to enter a random number into 20 cells, beginning with the active cell:

Sub FillRange()

Dim Count As Long

For Count = 0 To 19

ActiveCell.Offset(Count, 0) = Rnd

Next Count

End Sub

In this example, Count (the loop counter variable) starts with a value of 0 and increases by 1 each time through the loop. Because I didn’t specify a Step value, VBA uses the default value (1). The Offset method uses the value of Count as an argument. The first time through the loop, Count is 0 and the procedure enters a number into the active cell offset by zero rows. The second time through (Count = 1), the procedure enters a number into the active cell offset by one row, and so on.

Because the loop counter is a normal variable, you can write code to change its value within the block of code between the For and the Next statements. This, however, is a very bad practice. Changing the counter within the loop can have unpredictable results. Take special precautions to ensure that your code does not directly change the value of the loop counter.

A For-Next example with a Step

You can use a Step value to skip some values in a For-Next loop. Here’s the same procedure as in the preceding article, rewritten to insert random num-bers into every other cell:

Sub FillRange2()

Dim Count As Long

For Count = 0 To 19 Step 2

ActiveCell.Offset(Count, 0) = Rnd

Next Count

End Sub

This time, Count starts out as 0 and then takes on a value of 2, 4, 6, and so on. The final Count value is 18. The Step value determines how the counter is incremented. Notice that the upper loop value (19) is not used because the highest value of Count after 18 would be 20, and 20 is larger than 19.

Figure 10-2 shows the result of running FillRange2 when cell B2 is the active cell.

As, you saw the BadLoop example, which uses a GoTo statement. Here’s the same example,

Sub GoodLoop()

Dim StartVal As Long

Dim NumToFill As Long

Dim CellCount As Long

StartVal = InputBox(“Enter the starting value: “)

NumToFill = InputBox(“How many cells? “)

For CellCount = 1 To NumToFill

ActiveCell.Offset(CellCount - 1, 0) = _

StartVal + CellCount - 1

Next CellCount

End Sub

A For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately.

This routine identifies which of the active worksheet’s cells in column A has the largest value:

Sub ExitForDemo()

Dim MaxVal As Double

Dim Row As Long

MaxVal = WorksheetFunction.Max(Range(“A:A”))

For Row = 1 To Rows.Count

If Range(“A1”).Offset(Row-1, 0).Value = MaxVal Then Range(“A1”).Offset(Row-1, 0).Activate

MsgBox “Max value is in Row “ & Row

Exit For

End If

Next Row

End Sub

The routine calculates the maximum value in the column by using Excel’s MAX function and assigns the result to the MaxVal variable. The For-Next loop then checks each cell in the column. If the cell being checked is equal to MaxVal, the routine doesn’t need to continue looping (its job is finished), so the Exit For statement terminates the loop.

Now you might shout “Hey, but you said something about always using a single point of exit!” Well, you’re right, and obviously you’re getting the hang of this structured programming business. But in some cases, ignoring that rule is a wise decision. In this example it will greatly speed up your code because there’s no reason to continue the loop after the value is found.

Before terminating the loop, the procedure activates the cell with the maxi-mum value and informs the user of its location.

Notice that I use Rows.Count in the For statement. The count property of the Rows object returns the number of rows in the worksheet. Therefore, you can use this procedure with earlier versions of Excel (which have fewer rows).

A nested For-Next example

So far, all these examples use relatively simple loops. However, you can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.

The following example uses a nested For-Next loop to insert random numbers into a 12-row-x-5-column range of cells, as shown in Figure 10-3. Notice that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) = Rnd statement 60 times.

Sub FillRange2()

Dim Col As Long

Dim Row As Long

For Col = 1 To 5

For Row = 1 To 12

Cells(Row, Col) = Rnd

Next Row

Next Col

End Sub

The next example uses nested For-Next loops to initialize a three-dimensional array with the value 100. This routine executes the statement in the middle of all the loops (the assignment statement) 1,000 times (10 * 10 * 10), each time with a different combination of values for i, j, and k:

Sub NestedLoops()

Dim MyArray(10, 10, 10)

Dim i As Integer

Dim j As Integer

Dim k As Integer

For i = 1 To 10

For j = 1 To 10

For k = 1 To 10

MyArray(i, j, k) = 100

Next k

Next j

Next i

‘ Other statements go here End Sub

Do-While loop

VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met. Here’s the Do-While loop syntax:

Do [While condition]

[statements]

[Exit Do]

[statements]

Loop

The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 2. The loop continues until the routine encounters an empty cell.

Sub DoWhileDemo()

Do While ActiveCell.Value <> Empty

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Some people prefer to code a Do-While loop as a Do-Loop While loop. This example performs exactly as the previous procedure but uses a different loop syntax:

Sub DoLoopWhileDemo()

Do

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop While ActiveCell.Value <> Empty

End Sub

Here’s the key difference between the Do-While and Do-Loop While loops: The Do-While loop always performs its conditional test first. If the test is not true, the instructions inside the loop are never executed. The Do-Loop While loop, on the other hand, always performs its conditional test after the instructions inside the loop are executed. Thus, the loop instructions are always executed at least once, regardless of the test. This difference can sometimes have a big effect on how your program functions.

Do-Until loop

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program contin-ues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true.

Here’s the Do-Until syntax:

Do [Until condition]

statements]

[Exit Do]

[statements]

Loop

The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop:

Sub DoUntilDemo()

Do Until IsEmpty(ActiveCell.Value)

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Just like with the Do-While loop, you may encounter a different form of the Do-Until loop — a Do-Loop Until loop. The following example, which has the same effect as the preceding procedure, demonstrates an alternate syntax for this type of loop:

Sub DoLoopUntilDemo()

Do

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Value)

End Sub

There is a subtle difference in how the Do-Until loop and the Do-Loop Until loop operate. In the former, the test is performed at the beginning of the loop, before anything in the body of the loop is executed. This means that it is pos-sible that the code in the loop body will not be executed if the test condition is met. In the latter version, the condition is tested at the end of the loop. Therefore, at a minimum, the Do-Loop Until loop always results in the body of the loop being executed once.

Another way to think about it is like this: The Do-While loop keeps looping as long as the condition is True. The Do-Until loop keeps looping as long as the condition is False.