The Collection Object

You would already be familiar with using object collections; VBA is full of them. The TableDefs and QueryDefs collections are examples we use almost every day. These collections maintain a list of pointers to the individual objects they control; in fact collection objects are also referred to as Controllers.

To access an individual object within an object collection, you refer to its collection name and either the name of one of the objects it contains, or its ordinal position within the collection. For example, to check the date a table was created, you could use either of the following constructs:

Debug.

Print

CurrentDb.

.TableDefs("Table1").DateCreated

Debug.

Print

CurrentDb.

.TableDefs(1).DateCreated

Most collection objects implement the Add, Remove, and Item methods, and the Count property. Unfortunately, the name given to the Add method can vary from application to application, and even from object to object, but they essentially do the same thing (see Figure 12-20).

Figure 12-20

For example, to add a worksheet to an Excel workbook, you use the Add method of the Worksheets collection. By contrast, to add a table to the Access TableDefs collection, you create the new table object using CreateTableDef() and append it to the collection using the collection's Append method.

The VBA Collection object uses the Add method. Aren't standards a wonderful thing!

The Add Method

The Add method adds a member to the collection, and has the following syntax: Collection object name.Add (Item, [Key], [Before], [After]) The method parameter arguments are:

□ Item. As you can store almost anything in a collection, the Item parameter is an expression of any type that represents the member to add to the collection. In the case of object collections, this is where you supply a pointer to the object instance.

□ Key. The Key parameter is an optional unique string expression that specifies a key that can be used to identify the collection member, instead of using its ordinal position in the collection. It must be a unique string, otherwise an error occurs.

□ Before. The Before parameter allows you to optionally specify that the new member is to be added at the position immediately preceding an existing member in the collection. The parameter you supply can be either the ordinal numeric position of the existing member, or the Key that the existing member was saved with when it was added to the collection. If specifying a number, it must be in the range between 1 and the collection's Count property. You can specify a Before or After position, but not both.

□ After. Similar to the Before parameter, the After parameter specifies that the new member is to be added at the position immediately following an existing collection member. The parameter you supply can be either the ordinal numeric position of the existing member, or the Key that the existing member was saved with when it was added to the collection. If specifying a number, it must be in the range between 1 and the collection's Count property. You can specify a Before or After position, but not both.

The Remove Method

The Remove method allows you to remove a member from the collection, and uses the following syntax:

object.Remove(index)

The index argument specifies the position of an existing collection member. The argument you supply can be either the ordinal numeric position of the existing member, or the Key that the existing member was saved with when it was added to the collection. If specifying a number, it must be in the range between 1 and the collection's Count property.

Note that this is in contrast to the built-in Access collections (like the TableDefs collection), which are zero-based (start at zero).

The Item Method

The Item method provides a way for you to specify an object by its ordinal position in the collection. It uses the following syntax:

object.Item(index)

The index argument specifies the position of an existing collection member. The argument you supply can be either the ordinal numeric position of the existing member, or the Key that the existing member was saved with when it was added to the collection. If specifying a number, it must be in the range between 1 and the collection's Count property.

Note that this is in contrast to the built-in Access collections (like the TableDefs collection), which are zero-based (start at zero).

As you add and remove members from the middle of the collection, the Item numbers of all the members around it are renumbered to maintain continuity. For example, if you remove item 2 from a four-member collection, item 3 is renumbered to 2, and item 4 is renumbered to 3. It is for this reason that you should not rely on an object's ordinal position in the collection.

The Count Property

The Count property returns a Long Integer containing the number of objects in a collection, and is read-only.

0 -1

Post a comment