Using Loops

   

It often happens in VBA code particularly code that is intended to add, edit, copy, move, or delete data values that you want to make reference to many values or cells or rows, one at a time. You might want to add recently obtained records to a set that already exists, or to edit many values by concatenating onto each a string such as @mydomain.com, or to add new records to a database table and assign them values from your worksheet.

By designing loops correctly, you can carry out actions a huge number of times and get your results with great speed and accuracy.

Using For-Next Loops

A For-Next loop is a fundamental structure in VBA code. It's a basic way of taking an action or set of actions a specified number of times. Here's a straightforward example:

 For RowCounter = 1 To 10         Cells(RowCounter, 1) = RowCounter Next RowCounter 

The statement inside the loop, between the For statement and the Next statement, assigns a value to a cell. This fragment

 Cells(RowCounter, 1) 

refers to a cell in the RowCounter row and in column 1. After the Cells keyword, the first number in parenthesis specifies the row, and the second number specifies the column.

This loop runs 10 times:

  1. The For statement specifies that RowCounter starts with a value of 1.

  2. Any statements between the For and the Next are executed. In this example, the current value of RowCounter is put into the cell defined by the RowCounter row and the first column.

  3. The Next statement increments RowCounter by 1, and control of the program returns to the top of the loop.

  4. The loop executes again, with RowCounter equal to 2. Then it executes again, with RowCounter equal to 3. Each time, it executes any statements found between the For statement and the Next statement.

After 10 circuits through the loop, the value of RowCounter is incremented to 11. But this is one more than the final value specified by the For statement, so the loop terminates before it can run an eleventh time. The result is that on the active sheet, cell A1 contains 1, cell A2 contains 2,…, cell A10 contains 10.

This is nearly a trivial use of a For-Next loop, of course, but it illustrates the basic idea and some of the aspects of a For-Next loop. And simple as it is, it has its uses. Suppose that you're using the loop to return records, one by one, from a table in a database and into a worksheet. You want the worksheet to associate the records' values on a specific field with their record numbers. One good way to show record numbers is to put the record number in column A, as in this example, and the record's value on a field such as Age in column B. Using the counter's value on each circuit through the loop is a handy way to provide a record number.

Looping with Item Numbers

Earlier this chapter cautioned you about using an item number to refer to a particular object. When you first write a statement, Worksheets(2) might well refer to the worksheet named Sheet2. But if later another worksheet is inserted before Sheet2, there's no telling which worksheet that Worksheets(2) refers to.

But it's a different situation when you want to refer, in turn, to each object in a collection. In that case, you're not interested so much in a particular worksheet, say, as you are in addressing all the worksheets, one by one. Then an item number used in conjunction with a loop makes good sense. Here's an example:

 Option Explicit Sub RefreshAllPivotTables() Dim i As Integer, j As Integer Dim SheetCount As Integer, PivotTableCount As Integer 

The code begins by declaring four variables. The first two, i and j, will be used as counters in For-Next loops. The second two, SheetCount and PTCount, will be used to determine how many times the loops execute.

 SheetCount = ActiveWorkbook.Worksheets.Count 

This statement sets the variable SheetCount to a particular value: the number of worksheets in the active workbook. The dot notation begins with the active workbook, and moves from there to the collection of worksheets in the active workbook.

The collection of worksheets, as is true of all collections, has the Count property. In this case, the Count property returns the number of worksheets in the active workbook. That number is assigned to the variable SheetCount.

 For i = 1 To SheetCount 

The code enters a For-Next loop. It will execute once for every worksheet in the active workbook. The counter i starts at 1 the first time through, increments to 2 the next time, 3 the next time, and so on until it's greater than the value of SheetCount.

 ActiveWorkbook.Worksheets(i).Activate 

This statement activates a worksheet in the active workbook. Which worksheet is activated depends on the current value of i: the first, the second, the third, and so on.

 PivotTableCount = ActiveSheet.PivotTables.Count 

The active sheet might have any number of pivot tables: zero, one, five you don't know and for the purposes of this code you don't care. Just as the variable SheetCount was set to the number of worksheets in the active workbook, the variable PivotTableCount is set to the number of pivot tables in the active sheet.

Notice, though, that the value of SheetCount is set only once, before any loops are entered. The value of PivotTableCount is reset once again, each time a new worksheet is activated. VBA does all the counting on your behalf: It counts the number of worksheets to activate, and on each worksheet it counts the number of pivot tables to refresh.

Next an inner For-Next loop starts. The counter j begins at 1 and increments by 1 as it loops through all the pivot tables on the active worksheet, as determined by the current value of PivotTableCount.

 For j = 1 To PivotTableCount     ActiveSheet.PivotTables(j).PivotCache.Refresh Next j 

The inner For-Next loop refreshes the cache of each pivot table on the active sheet. There are three points about this loop to note in particular:

  • It's an inner loop; that is, it exists inside another loop. This sort of structure is often termed a nested loop, and it's a useful technique any time you're dealing with a collection (here, pivot tables) that's subordinate to another collection (here, worksheets).

  • The inner loop is controlled by a different counter than the outer loop. It would be syntactically illegal (as well as logically senseless) to use i for both the inner and outer loop.

  • No harm is done if the active worksheet has no pivot tables. In that case, PivotTableCount has been set equal to zero, and the For statement runs the counter from 1 to 0 that is to say, not at all, so it doesn't try to refresh any pivot tables.

TIP

A For counter can run from 1 to 0, but you have to arrange for that by adding something like Step -1 to the For statement: For j = 1 to 0 Step -1. The default is to increase the counter by 1 each time the loop executes.


Finally,

 Next i 

causes the outer loop to continue to the next value of its loop counter.

 End Sub 

End the subroutine after the outer loop terminates.

Notice the way that the subroutine is designed: It activates every worksheet in the workbook, one by one. When a given worksheet has been activated, it refreshes every pivot table on that worksheet, one by one. So, you're not setting yourself up for a problem as you might by referring to a specific item number such as Worksheets(2) or Worksheets("Sheet1").PivotTables(3).

Looping with Do While

For-Next loops give your code structure; that is, they execute a given number of times. Even if you don't know beforehand how many times a loop will execute, you're forced to provide an endpoint for the loop. In the last example, the endpoints were SheetCount for the outer loop and PivotTableCount for the inner loop. VBA evaluates those endpoints before the loop begins to run, and therefore has determined exactly how many times it will execute.

A different situation calls for a different sort of approach. You often find that you need to loop through subsets of records, and each subset can have a different number of records. That situation is discussed in the following case study.



Managing Data with Microsoft Excel
Managing Data with Microsoft Excel
ISBN: 789731002
EAN: N/A
Year: 2003
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net