MS-Excel / General Formatting

---

Using For...Next Loops

The most common type of loop is the For...Next loop. Use this loop when you know exactly how many times you want to repeat a group of statements. The structure of a For...Next loop looks like this:

For counter = start To end [Step increment]
[statements]
Next [counter]

counter
A numeric variable used as a loop counter. The loop counter is a number that counts how many times the procedure has gone through the loop.

start
The initial value of counter. This is usually 1, but you can enter any value or you can use a variable.

end
The final value of counter. You can also use a variable here, if it's appropriate.

increment
This optional value defines an increment for the loop counter. If you leave this out, the default value is 1. Use a negative value to decrement counter.

statements
The statements to execute each time through the loop.

The basic idea is simple. When VBA encounters the For...Next statement, it follows this five-step process:

1. Set counter equal to start.
2. Test counter. If it's greater than end, exit the loop (that is, process the first statement after the Next statement). Otherwise, continue. If increment is negative, VBA checks to see whether counter is less than end.
3. Execute each statement between the For and Next statements.
4. Add increment to counter. Add 1 to counter if increment isn't specified.
5. Repeat steps 2 through 4 until done.

Listing shows a simple Sub procedure-LoopTest-that uses a For...Next statement. Each time through the loop, the procedure uses the Application object's StatusBar property to display the value of counter (the loop counter) in the status bar. When you run this procedure, counter gets incremented by 1 each time through the loop, and the new value gets displayed in the status bar.

A Simple For...Next Loop

Sub LoopTest()
    Dim counter
    For counter = 1 To 10
        '
        'Display the message
        '
        Application.StatusBar = "Counter value: " & counter
        '
        ' Wait for 1 second
        '
        Application.Wait Now + TimeValue("00:00:01")
    Next counter
    Application.StatusBar = False
End Sub

Note: The LoopTest procedure works fine in Excel, but it will fail in the other Office applications because they don't implement the Wait method. If you need to get your code to delay for a short while, here's a simple procedure that does the trick:

Sub VBAWait(delay As Integer)
    Dim startTime As Long
    startTime = Timer
    Do While Timer - startTime < delay
        DoEvents
    Loop
End Sub

Note the use of the DoEvents function inside the Do While...Loop structure.This function yields execution to the operating system so that events such as keystrokes and application messages are processed while the procedure delays.

Here are some notes on For...Next loops:

• If you use a positive number for increment (or if you omit increment), end must be greater than or equal to start. If you use a negative number for increment, end must be less than or equal to start.
• If start equals end, the loop will execute once.
• As with If...Then...Else structures, indent the statements inside a For...Next loop for increased readability.
• To keep the number of variables defined in a procedure to a minimum, always try to use the same name for all your For...Next loop counters. The letters i through n traditionally are used for counters in programming. For greater clarity, you might want to use names such as "counter."
• For the fastest loops, don't use the counter name after the Next statement. If you'd like to keep the counter name for clarity (which I recommend), precede the name with an apostrophe (') to comment out the name, like this:

  For counter = 1 To 10
      [statements]
  Next 'counter
  

• If you need to break out of a For...Next loop before the defined number of repetitions is completed, use the Exit For statement, described in the section "Using Exit For or Exit Do to Exit a Loop."