Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
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 representing
a loop using line indentations is almost impossible. 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.
For-Next loops
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]
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
 
Search JabSto ::




Custom Search