Page 54 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

happen every time a particular event occurs, you can use VBA to make it happen and save
you the trouble.

Collections
The final element of object-oriented programming with which you should be familiar is the
collection. As the name implies, a collection is a group of objects of the same type that are
contained within another object. For example, a workbook contains a collection of one or
more worksheets. If you wanted to make a change to every worksheet in a workbook, you
could step through every worksheet in the collection and make the change programmatically.
If you’ve programmed before, you’ve probably run into the For…Next loop, which lets you
repeat a set of instructions a number of times using something like the following sequence,
which adds the directory path of the active workbook to the right section of the footer on the
first three worksheets:

For i = 1 to 3
Worksheets(i).PageSetup.RightFooter = Path
Next i

The problem with hard-coding (that is, assigning a set value to) the upper limit of a For…Next
loop is that you would need to change the code every time you added or deleted a worksheet.
Chapter 3
That’s no big deal once or twice, but if you’re managing a lot of code you’ll inevitably forget
to change it in a few places, causing errors you’ll have to fix. Worse yet, those errors might not
be noticed until the proofreader discovers that the first 500 printed copies of your annual
report aren’t formatted correctly and you’ve turned off your wireless phone as you while away
your time on the beach. Yes, you can use a bit of code to discover the number of worksheets
in your workbook, but there’s a simpler way to do it: use a For Each…Next loop instead. For
Each…Next loops find the number of objects in a collection, such as worksheets in a work-
book, and step through each occurrence. In this example, the preceding code would be writ-
ten this way.

For Each Wksht in Worksheets
Wksht.PageSetup.RightFooter = Path
Next Wksht

Instead of incrementing the value in a standard For…Next loop, the For Each…Next loop
simply looks for the next member of the Worksheets collection and stops when it doesn’t
find one.

For more information on For…Next and For Each…Next loops, see “Controlling Program Flow” in
Chapter 4, “VBA Programming Starter Kit.”

28
Part 2: Visual Basic for Applications