For ... loops execute for a fixed number of times. For... Next loops repeat for a number of times of your choosing, while For Each... Next loops execute once for each element in the specified VBA collection.
For... Next loops repeat an action or a sequence of actions a given number of times, specified by a counter variable. The counter variable can be hard-coded into the procedure, passed from an input box or dialog box, or passed from a value generated either by a different part of the procedure or by a different procedure.
The syntax for For... Next loops is as follows:
For counter = start To end [Step stepsize] [statements] [Exit For] [statements] Next [counter]
Table 12.2 explains the components of the syntax. As usual, the brackets show optional items and the italics show placeholders.
Component | Description |
---|---|
Counter | A numeric variable or an expression that produces a number. By default, VBA increases the counter value by an increment of 1 with each iteration of the loop, but you can change this increment by using the optional Step keyword and stepsize argument. counter is required in the For statement and is optional in the Next statement, but it's a good idea to include counter in the Next statement to make your code easy to read. This is particularly important when you're using multiple For... Next statements in the same procedure or nesting one For... Next statement within another. |
Start | A numeric variable or numeric expression giving the starting value for counter. |
End | A numeric variable or numeric expression giving the ending value for counter. |
Stepsize | A numeric variable or numeric expression specifying how much to increase or decrease the value of counter. To use stepsize, use the Step keyword and specify the stepsize variable. stepsize is 1 by default, but you can use any positive or negative value. |
Exit For | A statement for exiting a For loop. |
Next | The keyword indicating the end of the loop. Again, you can specify the optional counter here to make your code clear. |
Here's what happens in a For... Next loop:
When VBA enters the loop at the For statement, it assigns the start value to counter. It then executes the statements in the loop. When it reaches the Next statement, it increments counter by 1 or by the specified stepsize and loops back to the For statement.
VBA then checks the counter variable against the end variable. When stepsize is positive, if counter is greater than end, VBA terminates the loop and continues execution of the procedure with the statement immediately after the Next statement (which could be any action or the end of the procedure). If counter is less than or equal to end, VBA repeats the statements in the loop, increases counter by 1 or by stepsize, and loops back to the For statement again. (For a loop in which stepsize is negative, the loop continues while counter is greater than or equal to end and ends when counter is equal to or less than end.)
The Exit For statement exits the For loop early. You'll look at how to use the Exit For statement, and examples of the different uses of For... Next loops, later in this chapter.
In a straightforward For... Next loop, you first specify a counter variable and the starting and ending values for it:
Dim i As Integer For i = 1 to 200
Here, i is the counter variable, 1 is the starting value, and 200 is the ending value. By default, VBA increases the counter variable by 1 with each iteration of the loop. Here, it will be 1, 2, 3, and so on up to 200; a value of 201 (or greater — although in this example, it can't reach a greater value than 201 because the stepsize is 1) terminates the loop. You can also use the Step keyword to specify a different increment, either positive or negative; more on this in the next section.
I Is the Traditional Counter Variable Name for For...Next Loopsi is the classic integer counter variable used in a For... Next loop; after using i, the convention is to use j, k, l, m, and n for subsequent counter variables. The short names derive from the days of key cards, when longer names represented a significant inconvenience. These days, VBA makes using longer names easy. Use i and these other letters for your loops if you prefer compactness. Otherwise, use more descriptive variable names, such as LoopCounter or intLoopCounter, if you want to make your code easier to decipher. |
After the previous two statements, you specify the actions to perform in the loop, followed by the Next keyword to end the loop:
Application.StatusBar = _ "Please wait while Excel checks for nonuniform prices: " & i & "..." Next i
This code produces a status bar readout indicating Excel's progress in checking your spreadsheet for improbable values.
As another example, say you need to check every paragraph in Word documents you receive from contributors to make sure there's no unsuitable formatting. By using a loop that runs from 1 to the number of paragraphs in the active document (which is stored in the Count property of the Paragraphs collection in the ActiveDocument object), you can check each paragraph in turn and provide a reference point for the user in the status bar display:
Dim i As Integer For i = 1 To ActiveDocument.Paragraphs.Count CheckParagraphForIllegalFormatting Application.StatusBar = _ "Please wait while Word checks the formatting in " _ & " this document: Paragraph " & i & " out of " _ & ActiveDocument.Paragraphs.Count & "..." Selection.MoveDown Unit:=wdParagraph, _ Count:=1, Extend:=wdMove Next i
This code snippet should be started at the beginning of the document. It runs the CheckParagraphForIllegalFormatting procedure on the current paragraph, displays a message in the status bar indicating which paragraph out of the total number it's working on, and then moves down a paragraph. When VBA reaches the Next statement, it increases the i counter by the default value, 1 (because no stepsize variable is specified), and loops back to the For statement, where it compares the value of i to the value of ActiveDocument.Paragraphs.Count. The procedure continues to loop until i has reached the value of ActiveDocument.Paragraphs.Count, which is the final iteration of the loop.
Likewise, you could use a simple For... Next loop to quickly build the structure of a timesheet or work log in Excel. The following statements use a For... Next loop to insert the labels 1.00 through 24:00 in the current column in the active sheet of the active workbook:
Dim i As Integer For i = 1 To 24 ActiveCell.FormulaR1C1 = i & ":00" ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Select Next i
Here, the ActiveCell.FormulaR1Ci statement inserts the automatically increased string for the counter — i — together with a colon and two zeroes (to create a time format). The ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Select statement selects the cell in the next row and the same column. The loop runs from i = 1 to i = 24 and stops when the automatic increase takes i to 25.
If increasing the counter variable by the default 1 doesn't suit your purpose, you can use the Step keyword to specify a different increment or decrement. For example, the following statement increases the counter variable by 20, so the sequence is 0, 20, 40, 60, 80, 100:
For i = 0 to 100 Step 20
You can also use a decrement by specifying a negative Step value:
For i = 1000 to 0 Step −100
This statement produces the sequence 1000, 900, 800, and so on, down to 0.
Instead of the "x out of y" countdown example given in the previous section, you could produce a countdown running from ActiveDocument.Paragraphs.Count to zero:
Dim i As Integer For i = ActiveDocument.Paragraphs.Count To 0 Step −1 CheckParagraphForIllegalFormatting Application.StatusBar = _ "Please wait while Word checks the formatting in this document: " & i Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdMove Next i
Sometimes you'll be able to hard-code the number of iterations into a For... Next loop. Other times, you'll take a number from another operation, such as the ActiveDocument.Paragraphs.Count property in the previous example. But often you'll need to use input from the user to drive the loop. The easiest way of doing this is to have the user enter the value into an input box.
For example, Listing 12.1 contains a simple procedure named CreatePresentations that displays an input box prompting users to enter the number of presentations they want to create. It then uses a For... Next loop to create the documents in PowerPoint.
1. Sub CreatePresentations() 2. Dim intPresentations As Integer 3. Dim i As Integer 4. intPresentations = InputBox _ ("Enter the number of presentations to create:", _ "Create Presentations") 5. For i = 1 To intPresentations 6. Presentations.Add 7. Next i 8. End Sub |
Here's what happens in the CreatePresentations procedure in Listing 12.1:
Line 2 declares the Integer variable intPresentations, and line 3 declares the Integer variable i.
Line 4 displays an input box prompting users to enter the number of presentations they want to create.
Lines 5 through 7 contain a For... Next loop that runs from i = 1 to i = intPresentations with the default increment of 1 per iteration. Each iteration of the loop executes the Presentations.Add statement in line 6, creating a new presentation based on the default template.
The For Each... Next loop, which is unique to Visual Basic, has the same basic premise as the For... Next loop, namely that you're working with a known number of repetitions. In this case, though, the known number is the number of objects in a collection, such as the Slides collection in a presentation or the Documents collection of Word documents. So, using For Each means that you, the programmer, don't necessarily know the number of loop iterations in advance, but VBA will know during execution because it can query an object's Count property.
For example, you can choose to take an action for each Slide object in a presentation — you don't need to know how many slides are in the collection, provided there is at least one. (If there are none, nothing happens.)
The syntax for the For Each... Next statement is straightforward:
For Each object In collection [statements] [Exit For] [statements] Next [object]
VBA starts by evaluating the number of objects in the specified collection. It then executes the statements in the loop for the first of those objects. When it reaches the Next keyword, it loops back to the For Each line, reevaluates the number of objects, and performs further iterations as appropriate.
Here's an example: the Documents collection contains the open documents in Word. So you could create a straightforward procedure to close all the open documents by using a For Each... Next loop like this:
Dim Doc As Document For Each Doc in Documents Doc.Close SaveChanges:=wdSaveChanges Next
VBA closes each open document in turn by using the Close method. The statement uses the wdSaveChanges constant for the SaveChanges argument to specify that any unsaved changes in the document be saved when the document is closed. As long as there are open documents in the Documents collection, VBA repeats the loop, so it closes all open documents and then terminates the procedure.
This example provides a straightforward illustration of how a For Each... Next loop works, but you probably wouldn't want to use the example in practice; instead, you'd probably use the Close method with the Documents collection (which contains all the open documents) to close all the open documents more simply. However, you might use a For Each... Next loop to check each document for certain characteristics before closing it.
As you saw earlier in this chapter when looking at the syntax for For statements, you can use one or more Exit For statements to exit a For loop if a certain condition is met. Exit For statements are optional and are seldom necessary. If you find yourself needing to use Exit For statements in all your procedures, there's probably something wrong with the loops you're constructing. That said, you may sometimes find Exit For statements useful — for example, for when an error occurs in a procedure or when the user chooses to cancel a procedure.
On those occasions when you do need Exit For statements to exit a loop early, you'll typically use them with straightforward conditions. For example, in Word, if you wanted to close open windows until you reached a certain document that you knew to be open, you could use an Exit For statement like this:
Dim Doc As Document For Each Doc in Documents If Doc.Name = "Document1" Then Exit For Doc.Close Next Doc
This For Each... Next statement checks the Name property of the document to see if it's Document1; if it is, the Exit For statement causes VBA to exit the loop. Otherwise, VBA closes the document and returns to the start of the loop.
Use Multiple Exit For Statements if You WishYou can also use multiple Exit For statements if you need to. For example, you might need to check two or more conditions during the actions performed in the loop. |
3.138.192.11