Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 193

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

The following code, which enters consecutive numbers into a range, demonstrates what is considered to be 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 iCount variable, which keeps track of how many cells are filled, is less than the value of NumToFill, the program control loops back to DoAnother.

Sub BadLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim iCount As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal iCount = 1 DoAnother: ActiveCell.Offset(iCount, 0).Value = StartVal + iCount iCount = iCount + 1 If iCount < 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.) 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 has a tangled flow.

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