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

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 in the block of code executed between the For and Next statements. Changing the loop counter inside a loop, however, is a bad practice and can cause unpredictable results. You should take precautions to ensure that your code doesn't 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 in the loop is 99. When the loop ends, the value of Count is 101.

A Step value in a For-Next loop can also be negative. The procedure that follows deletes rows 2, 4, 6, 8, and 10 of the active worksheet:

Sub DeleteRows() Dim RowNum As Long For RowNum = 10 To 2 Step -2 Rows(RowNum).Delete Next RowNum End Sub

You may wonder why we used a negative Step value in the DeleteRows procedure. If you use a positive Step value, as shown in the following procedure, incorrect rows are deleted. That's because the rows below a deleted row get a new row number. For example, when row 2 is deleted, row 3 becomes the new row 2. Using a negative Step value ensures that the correct rows are deleted.

Sub DeleteRows2() Dim RowNum As Long For RowNum = 2 To 10 Step 2 Rows(RowNum).Delete Next RowNum End Sub

The following procedure performs the same task as the BadLoop example at the beginning of the “Looping blocks of instructions” section. We eliminate 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 iCount As Integer StartVal = 1 NumToFill = 100 For iCount = 0 To NumToFill - 1 ActiveCell.Offset(iCount, 0).Value = StartVal + iCount Next iCount End Sub

For-Next loops can also include one or more Exit For statements in 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 the 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 MaxVal = Application.WorksheetFunction.Max(Range("A:A")) For Row = 1 To 1048576 If Cells(Row, 1).Value = MaxVal Then Exit For End If Next Row MsgBox "Max value is in Row " & Row Cells(Row, 1).Activate End Sub

The maximum value in the column is calculated by using the Excel MAX function, and the value is 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 terminates the loop, and the statements following the Next statement are executed. These statements display the row of the maximum value and activate the cell.