Page 59 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 34

34 Friday Evening

When you reference a collection member, you can use the reference in two ways. One is
directly, as in the following line of code that causes the specific worksheet to recalculate all
of its formulas.

Sheets(“SalesData”).Calculate
You can also assign the reference to a variable and then use the variable to refer to the
object. For example:
Set MyWorksheet = Sheets(“SalesData”)
This assumes that the variable MyWorksheet has been created as a type of variable that
can hold a sheet reference You learn about this in Session 4. Note the use of the Set key-
word, which is required whenever assigning object references. The result is that
MyWorksheet refers to the worksheet “SalesData” and could be used as follows:
MyWorksheet.Calculate

Even when there are two or more references to an object, there is still only
one object.
Note
Most collections provide methods to add new objects to the collection and also to delete
objects. Adding an object uses the Add method. The syntax is:

CollectionName.Add(arguments)
Arguments provides details on how the object is to be created and differ depending on
the specific collection in use. The Add method returns a reference to the newly created
object, which you can ignore if you do not need it. To delete an object, use the Delete
method.
A particularly useful tool when working with collections is the For Each In statement.
This statement sets up a code loop that repeats once for each item in a collection. The syn-
tax is as follows:
For Each Item In Collection
...
Next
Item is a variable that has been declared as the correct data type to refer to the contents
of the collection. Collection is a reference to the collection. Item is set to reference the first
element in the collection, and the code in the loop (indicated by the ...) is executed. When
the Next statement is reached, execution loops back to the For Each statement, Item is set
to refer to the next element in the collection, and the process repeats until all elements in
the collection have been processed. If the collection is empty to begin with, execution sim-
ply skips over the loop. An example of using this loop is presented in the section on the
Workbook object later in this session.
This has been a quick introduction to collections. You’ll learn more of the details where
specific collections are covered throughout the book.