Working with Collections

A set of similar objects is known as a collection. For example, a Microsoft Access database has a collection of tables, and each table has a collection of fields and indexes. In Microsoft Excel, all open workbooks belong to the collection of workbooks, and all the sheets in a particular workbook are the members of the worksheets collection. In Microsoft Word, all open documents belong to the collection of documents, and each paragraph in a document is a member of the paragraphs collection. Collections are objects that contain other objects.

No matter what collection you want to work with, you can do the following: Refer to a specific object in a collection by using an index value.

For example, to get the name of the first control on the Customers form in the Northwind database, enter the following instruction in the Immediate window:


Determine the number of items in the collection by using the Count property.

For example, when you enter this statement in the Immediate window: ?Forms!Customers.Controls.Count

VBA will return the total number of controls in the Customers form. Insert new items into the collection by using the Add method.

The following example uses the Immediate window to create a new collection named myTestCollection and adds two new items to the collection:

set myTestCollection = New Collection myTestCollection.Add "first member" myTestCollection.Add "second member"

The following instruction returns the total number of items in myTestCollection:

?myTestCollection.Count 2

To find out the names of the collection members, you can type the following statements in the Immediate window:

?myTestCollection.Item(1) first member ?myTestCollection(2) second member

You can cycle through every object in the collection by using the For Each.. .Next loop.

Introduction to Access 2003 VBA Programming

For example, to remove all items from myTestCollection, enter the following looping structure in the Immediate window:

For Each m in myTestCollection : myTestCollection.Remove 1 : Next

If you did not get an error upon pressing Enter, myTestCollection should have zero members. However, to be sure, enter the following statement in the Immediate window:

?myTestCollection.Count 0

While writing your own VBA procedures, you may come across a situation where there's no built-in collection to handle the task at hand. The solution is to create a custom collection. From the previous chapter you already know how to work with multiple items of data by using dynamic or static arrays. Because collections have built-in properties and methods that allow you to add, remove, and count their elements, it's much easier to work with collections than arrays.

0 0

Post a comment