Microsoft Office 2000/Visual Basic Programmer's Guide   

Creating Collection Classes

You can represent a collection with a class module, and use the collection to organize objects of the same type. By convention, a collection class is usually given the plural name of the object that it contains; for example, the BoundLists collection contains BoundList objects.

Collection classes usually contain a Count property, for counting the number of objects in the collection, and an Item property, which returns a reference to an object in the collection by its index or key value. Most collection classes also contain an Add method, which adds a new object to the collection and returns a reference to it, and a Remove method, which removes an item from the collection.

You should consider how a top-level collection or object is to be initialized. Somewhere in your project there must be a line of code that creates an instance of the top-level collection or object. From there all other objects can be created from within your object model. If the collection will contain objects that may already exist or are saved with the file, such as bound list boxes or combo boxes, the collection will probably have to be initialized when the solution loads. If the collection provides objects on demand, it may not have to be initialized until the objects are needed.

You should also consider how objects are to be added to a collection. Does this happen based on user or programmatic input, or based on some change in the state of the system, or both? And do objects need to be added when the solution is first loaded? For example, the ListComboWiz.xla sample application, available in the ODETools\V9\Samples\OPG\Samples\CH11 subfolder on the Office 2000 Developer CD-ROM, includes two custom classes, one that represents a bound list box or combo box and one that represents a collection of these objects. The collection needs to add any existing bound lists when the solution opens, and new bound lists need to be added when the user creates them. The Collection object is initialized in the Workbook_Open event, and existing objects are added to the collection in the Class_Initialize event for the Collection object.