For Loops

When you know the number of iterations required from a loop, the For/Next loop is the best choice of structures. The syntax is very simple.

For variable = start To end Step value

'Block of code Next variable

The required keywords are For, To, and Next. To keep track of the number of iterations through the loop requires a counting variable as well as starting and ending values. The keyword Step is optional but if it's used, the value that follows it is used to denote the step size of the counting variable with each iteration through the loop. The step's value can be any positive or negative integer; the default value is +1 when Step is omitted. Table 4.1 lists a few examples of For/Next loops.

Table 4.1 Examples of For/Next Loops in VBA

Loop Example

MsgBox (I) Next I

MsgBox (I) Next I

MsgBox (I) Next I

Output from Message Box

11 iterations: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10

The variable I in Table 4.1 should be declared as an integer prior to use and the ending value for the loop is usually another variable rather than a constant. In most cases, you will use the default step size of +1, so the keyword Step is omitted.

Use the statement Exit Do or Exit For to force code execution to Leave a Looping structure and proceed with the first Line of code after the Loop. NormaLLy, Exit Do or Exit For wiLL be within a branching structure (If/Then or Select/Case) inside of the Loop.

The following example of a VBA function mimics the FACT() function in the Excel application by calculating the factorial of an integer.

Public Function Factorial(myValue As Integer) As Long Dim I As Integer Dim factorialValue As Long factorialValue = 1 For I = 2 To myValue factorialValue = factorialValue * I Next I

Factorial = factorialValue End Function

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment