Microsoft Office Tutorials and References
In Depth Information
When you use For-Next loops, you should understand that the loop counter is a normal
variable — it is not a special type of variable. As a result, you can change the value of
the loop counter within the block of code executed between the For and Next state-
ments. this is, however, a very bad practice and can cause problems. In fact, you
should take special precautions to ensure that your code does not change the loop
counter.
You also can use a Step value to skip some values in the loop. Here's the same function rewritten to sum every
other integer between the first and last arguments:
Function SUMINTEGERS2(first, last)
total = 0
For num = first To last Step 2
total = total + num
Next num
SUMINTEGERS2 = Total
End Function
The following formula returns 25 , which is the sum of 1, 3, 5, 7, and 9:
=SUMINTEGERS2(1,10)
For-Next loops can also include one or more Exit For statements within the loop. When this statement is en-
countered, the loop terminates immediately, as the following example demonstrates:
Function ROWOFLARGEST(c)
NumRows = Rows.Count
MaxVal = WorksheetFunction.Max(Columns(c))
For r = 1 To NumRows
If Cells(r, c) = MaxVal Then
ROWOFLARGEST = r
Exit For
End If
Next r
End Function
The ROWOFLARGEST function accepts a column number (1–16,384) for its argument and returns the row
number of the largest value in that column. It starts by getting a count of the number of rows in the worksheet.
(This varies, depending on the version of Excel.) This number is assigned to the NumRows variable. The max-
imum value in the column is calculated by using the Excel MAX function, and this value is assigned to the
MaxVal variable.
Search JabSto ::

Custom Search