12.3. Using For... Loops for Fixed Repetitions

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.

12.3.1. For... Next Loops

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.

12.3.1.1. Syntax

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.

Table 12.2. Components of the Syntax for a For... Next Loop
ComponentDescription
CounterA 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.
StartA numeric variable or numeric expression giving the starting value for counter.
EndA numeric variable or numeric expression giving the ending value for counter.
StepsizeA 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 ForA statement for exiting a For loop.
NextThe 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:

  1. 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.

  2. 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.)

  3. 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.

12.3.1.2. Straightforward For... Next Loops

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 Loops

i 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.

12.3.1.3. For... Next Loops with Step Values

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

12.3.1.4. Using an Input Box to Drive a For... Next Loop

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.

Example 12.1. Listing 12.1
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.

Control a For...Next Loop with User Input via a Dialog Box

For those occasions when an input box won't suffice, you can easily use a value from a dialog box to drive a For... Next loop. This book hasn't yet shown you how to create dialog boxes, but in this section you'll get a sneak preview by looking at a Create_Folders procedure designed to reduce the tedium of creating multiple folders with predictable names, such as for the sections of a multipart project.

For example, say that you're using a four-digit number to identify the project, the letter s for section, and a two-digit number to identify the section. So you'd end up with folders named 1234s01, 1234s02, 1234s03, and so on — simple enough to create manually, but very boring if you needed more than a dozen or so.

In its simplest form, this dialog box would provide a text box for the number of folders to be created (though you could also use a drop-down list for this, or even a spinner) and a text box for the project number. The following illustration is an example of how this dialog box might look:



You display the dialog box by using the Show method, perhaps with a Load statement first, like this:

Load frmCreateFolders
frmCreateFolders.Show

The example dialog box is called frmCreateFolders; any valid VBA name will work. The first text box — identified with the Number of Folders to Create label — is named txtFolders; the second text box is named txtProjectNumber.

The Cancel button here has an End statement attached to its Click event, so that if the user clicks it, VBA ends the procedure:

Private Sub cmdCancel_Click()
    End
End Sub

The OK button in the dialog box has the following code attached to its Click event:

1.  Private Sub cmdOK_Click()
2.
3.      Dim strMsg As String
4.      Dim strFolder As String
5.      Dim i As Integer
6.
7.      frmCreateFolders.Hide
8.      Unload frmCreateFolders
9.      strMsg = "The Create_Folders procedure has created " _
            & "the following folders: " & vbCr & vbCr
10.
11.     For i = 1 To txtFolders.Value
12.         strFolder = txtProjectNumber.Value & "p" & Format(i, "0#")
13.         MkDir strFolder
14.         strMsg = strMsg & "   " & strFolder & vbCr
15.     Next i
16.
17.     MsgBox strMsg, vbOKOnly + vbInformation, _
            "Create Folders"
18.
19.  End Sub

The cmdOK_Click procedure runs when the user clicks the OK button in the dialog box:

  • Line 1 declares the cmdOK_Click subroutine, and line 19 ends it. Line 2 is a spacer.

  • Line 3 declares the String variable strMsg, which is used to contain a string to display in a message box at the end of the procedure.

  • Line 4 declares the String variable strFolder, which will contain the name of the current folder to create in each iteration of the loop.

  • Line 5 declares the Integer variable i, which will be the counter variable for the For... Next loop.

  • Line 6 is a spacer.

  • Line 7 hides frmCreateFolders.

  • Line 8 unloads frmCreateFolders.

  • Line 9 assigns some introductory text to strMsg, ending it with a colon and two vbCr carriage-return characters to make the start of a list.

  • Line 10 is a spacer.

  • Lines 11 through 15 contain the For... Next loop that creates the folders. Line 11 causes the loop to run from i = 1 to i = txtFolders.Value, the value supplied by the user in the Number of Folders to Create text box. Line 12 assigns to the strFolder String variable the Value property of the txtProjectNumber text box, the letter p, and the value of i formatted via the Format function to include a leading zero if it's a single digit (so that 1 will appear as 01, and so on). Line 13 uses the MkDir command with strFolder to create a folder (that is, make a directory — the old DOS command mkdir lives on in VBA) of that name. Line 14 adds some spaces (for an indent), the contents of strFolder, and a vbCr character to strMsg. Line 15 then loops back to the For statement, incrementing the i counter. VBA then compares the i counter to txtFolders.Value and repeats the loop as necessary.

This procedure creates the new folders in the current folder, without giving the user a choice of location. Chances are you won't want to do this in real-life situations. You might want to change a folder to a set location (so as to keep all the project files together), but more likely you'll want to let the user choose a suitable location — for example, by displaying a common dialog box, such as the Save As dialog box used by most Windows applications.


12.3.2. For Each... Next Loops

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.)

12.3.2.1. Syntax

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.

12.3.3. Using an Exit For Statement

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 Wish

You 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.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.192.11