Looping blocks of instructions

Looping is the process of repeating a block of instructions. You might know the number of times to loop, or it could be determined by the values of variables in your program.

The following code, which enters consecutive numbers into a range, demonstrates what I call a bad loop. The procedure uses two variables to store a starting value (StartVal) and the total number of cells to fill (NumToFill). This loop uses the GoTo statement to control the flow. If the Cnt variable, which keeps track of how many cells are filled, is less than the number requested by the user, program control loops back to DoAnother.

Sub BadLoop()

Dim StartVal As Integer Dim NumToFill As Integer Dim Cnt As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal Cnt = 1 DoAnother:

ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Cnt = Cnt + 1

If Cnt < NumToFill Then GoTo DoAnother Else Exit Sub End Sub

This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding (see the "What Is Structured Programming?" sidebar). In fact, a GoTo statement makes the code much more difficult to read because it's almost impossible to represent a loop using line indentations. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti code —code that appears to have little or no structure and flows haphazardly.

Because VBA has several structured looping commands, you almost never have to rely on GoTo statements for your decision making.


The simplest type of a good loop is a For-Next loop. Its syntax is

For counter = start To end [Step stepval] [instructions] [Exit For] [instructions] Next [counter]

What Is Structured Programming?

Hang around with programmers, and sooner or later you'll hear the term structured programming. You'll also discover that structured programs are considered superior to unstructured programs.

So what is structured programming? And can you do it with VBA?

The basic premise is that a routine or code segment should have only one entry point and one exit point. In other words, a body of code should be a standalone unit, and program control should not jump into or exit from the middle of this unit. As a result, structured programming rules out the GoTo statement. When you write structured code, your program progresses in an orderly manner and is easy to follow — as opposed to spaghetti code, where a program jumps around.

A structured program is easier to read and understand than an unstructured one. More important, it's also easier to modify.

VBA is a structured language. It offers standard structured constructs, such as If-

Then-Else and Select Case, and the For-Next, Do Until, and Do While loops. Furthermore, VBA fully supports modular code construction.

If you're new to programming, it's a good idea to form good structured programming habits early.

Following is an example of a For-Next loop that doesn't use the optional Step value or the optional Exit For statement. This routine executes the Sum = Sum + Sqr(Count) statement 100 times and displays the result —that is, the sum of the square roots of the first 100 integers.

Sub SumSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0

For Count = 1 To 100

Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub

In this example, Count (the loop counter variable) started out as 1 and increased by 1 each time the loop repeated. The Sum variable simply accumulates the square roots of each value of Count.

When you use For-Next loops,it's important to understand that the loop counter is a normal variable — nothing special. As a result, it's possible to change the value of the loop counter within the block of code executed between the For and Next statements.This is,however,a bad practice and can cause unpredictable results. In fact, you should take special precautions to ensure that your code does not change the loop counter.

You can also use a Step value to skip some values in the loop. Here's the same procedure rewritten to sum the square roots of the odd numbers between 1 and 100:

Sub SumOddSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0

For Count = 1 To 100 Step 2

Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub

In this procedure, Count starts out as 1 and then takes on values of 3, 5, 7, and so on. The final value of Count used within the loop is 99. When the loop ends, the value of Count is 101.

The following procedure performs the same task as the BadLoop example found at the beginning of the "Looping blocks of instructions" section. I eliminated the GoTo statement, however, converting a bad loop into a good loop that uses the For-Next structure.

Sub GoodLoop()

Dim StartVal As Integer

Dim NumToFill As Integer

Dim Cnt As Integer

StartVal = 1

NumToFill = 100

ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Next Cnt End Sub

For-Next loops can also include one or more Exit For statements within the loop. When this statement is encountered, the loop terminates immediately and control passes to the statement following the Next statement of the current For-Next loop. The following example demonstrates use of the Exit For statement. This procedure determines which cell has the largest value in column A of the active worksheet:

Sub ExitForDemo()

Dim MaxVal As Double Dim Row As Long Dim TheCell As Range

MaxVal = Application.WorksheetFunction.Max(Range("A:A")) For Row = 1 To 65536

Set TheCell = Range("A1").Offset(Row - 1, 0) If TheCell.Value = MaxVal Then

MsgBox "Max value is in Row " & Row TheCell.Activate Exit For End If Next Row End Sub

The maximum value in the column is calculated by using the Excel MAX function. This value is then assigned to the MaxVal variable. The For-Next loop checks each cell in the column. If the cell being checked is equal to MaxVal, the Exit For statement ends the procedure. Before terminating the loop, though, the procedure informs the user of the row location and then activates the cell.

The ExitForDemo is presented to demonstrate how to use exit from a For-Next loop. However, it is not the most efficient way to activate the largest value in a range. In fact, a single statement will do the job:

Range("A:A").Find(Application.WorksheetFunction.Max _ (Range("A:A"))).Activate

The previous examples use relatively simple loops. But you can have any number of statements in the loop, and you can even nest For-Next loops inside other For-Next loops. Here's an example that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value -l. When the procedure is finished, each of the 1,000 elements in MyArray will contain -1.

Sub NestedLoops()

Dim MyArray(l to l0, l to l0, l to l0) Dim i As Integer, j As Integer, k As Integer For i = l To l0


A Do While loop is another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes while a specified condition is met. A Do While loop can have either of two syntaxes:

Do [While condition] [instructions] [Exit Do] [instructions]

Loop or

[instructions] [Exit Do] [instructions] Loop [While condition]

As you can see, VBA lets you put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed. In the second syntax, the contents of the loop are always executed at least one time.

The following example uses a Do While loop with the first syntax.

Sub DoWhileDemo()

Do While Not IsEmpty(ActiveCell) ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select

Loop End Sub

This procedure uses the active cell as a starting point and then travels down the column, inserting a zero into the active cell. Each time the loop repeats, the next cell in the column becomes the active cell. The loop continues until VBA's IsEmpty function determines that the active cell is empty.

The following procedure uses the second Do While loop syntax. The loop will always be executed at least one time, even if the initial active cell is empty.

Sub DoWhileDemo2() Do

ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select Loop While Not IsEmpty(ActiveCell) End Sub

The following is another Do While loop example. This procedure opens a text file, reads each line, converts the text to uppercase, and then stores it in the active sheet, beginning with cell A1 and continuing down the column. The procedure uses the VBA EOF function, which returns True when the end of the file has been reached. The final statement closes the text file.

Sub DoWhileDemo1()

Dim LineCt As Long

Open "c:\data\textfile.txt" For Input As #1 LineCt = 0 Do While Not EOF(1)

Line Input #1, LineOfText

Range("A1").Offset(LineCt, 0) = UCase(LineOfText) LineCt = LineCt + 1


Close #1

End Sub

For additional information about reading and writing text files using VBA, see Chapter 27.

Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately and control passes to the statement following the Loop statement.

The Do Until loop structure is very similar to the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is True. In a Do Until loop, the loop executes until the condition is True.

Do Until also has two syntaxes:

Do [Until condition] [instructions] [Exit Do] [instructions]

Loop or

[instructions] [Exit Do] [instructions] Loop [Until condition]

The following example was originally presented for the Do While loop but has been rewritten to use a Do Until loop. The only difference is the line with the Do statement. This example makes the code a bit clearer because it avoids the negative required in the Do While example.


Sub DoUntilDemo1()

Dim LineCt As Long

Open "c:\data\textfile.txt" For Input As #1 LineCt = 0 Do Until EOF(1)

Line Input #1, LineOfText

Range("A1").Offset(LineCt, 0) = UCase(LineOfText) LineCt = LineCt + 1


Close #1 End Sub

Chapter 9

Was this article helpful?

0 0


Post a comment