Knocking Your Code for a Loop

The term looping refers to repeating a block of VBA statements numerous times. You may know how many times your program needs to loop, or variables used in your program's may determine this.

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 Integer

Dim NumToFill As Long

Dim CellCount As Long

StartVal = CInt(InputBox("Enter the starting

value: "))

NumToFill = CInt(InputBox("How many cells? "


ActiveCell = StartVal

CellCount = 1


ActiveCell.Offset(CellCount, 0) = StartVal +


CellCount = CellCount + 1

If CellCount < NumToFill Then GoTo DoAnother

Else Exit Sub

End Sub

This routine works as intended, so why is it an example of bad looping? As I mention earlier in this chapter, avoid using a GoTo statement unless it's absolutely necessary. Using GoTo statements to perform looping i Is contrary to the concept of structured programming. (See the sidebar earlier in this chapter, "What is structured programming? Does it matter?")

i Makes the code more difficult to read.

i 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.

0 0

Post a comment