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 code 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 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, 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
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.
