Microsoft Office Tutorials and References
In Depth Information
9.2.1 The Base of a Collection
ChartObjects
PivotFields
Workbooks
Charts
PivotTables
Worksheets
DataLabels
Points
Filters
Range
We emphasize the fact that a collection is just a special type of object. Indeed, the properties and
methods of a Collection object are specifically designed to manage the collection. Accordingly,
the basic requirements for a collection object are:
A property called Count that returns the number of objects in the collection. This is a
read-only property; that is, it cannot be set by the programmer. It is automatically updated
by VBA itself.
A method called Add (or something similar, such as AddNew) that allows the
programmer to add a new object to the collection.
A method called Remove, Close, or Delete, or something similar, that allows the
programmer to remove an object from the collection.
A method called Item that permits the programmer to access any particular object in the
collection. The item is usually identified either by name or by an index number.
Note that these basic requirements are not hard and fast. Some collection objects may not
implement all of these members, and many implement additional members. For instance, the
Areas and Borders collections do not have an Add method, since we are not allowed to add objects
to these collections. We can only manipulate the properties of these collections.
Some Excel collections are considerably more complicated than others, since they have several
properties and methods that relate specifically to the type of object they contain. For instance, the
Sheets collection has 10 properties and 8 methods. Several of these members, such as the PrintOut
method, are included specifically so that they can operate on all of the sheets in the collection at
the same time. (A sheet is either a worksheet or a chartsheet.)
9.2.1 The Base of a Collection
Note that collections can be either 0-based or 1-based. In a 0-based collection , the first member
has index 0, and in a 1-based collection , the first member has index 1. Most, but not all,
collections in the Excel object model and in VBA itself are 1-based. However, some older
collections tend to be 0-based. (I guess that Microsoft got a lot of complaints about 0-based
collections so they decided to switch.)
It is important to determine the base of any collection before trying to access members by index.
This can be done by checking the help system (sometimes) or trying some sample code. For
instance, the code:
For i = 1 To Selection.Cells.Count
Debug.Print Selection.Cells(i).Value
Next i
is correct, since the Cells collection is 1-based. However, the UserForms collection, which
represents all currently loaded user forms in Excel, is 0-based, so the code:
For i = 1 To UserForms.Count
Debug.Print UserForms(i).Name
Next i
will produce an error. The correct code is:
Search JabSto ::




Custom Search