Collections

VBA includes a collection object that you can use to create and manipulate your own custom collections.
A collection is a special type of object that represents a group of objects.
A Collection object is an ordered set of items that can be referred to as a unit.
You can think of a collection as an array and refer to items using their index numbers or, items can be referred to by using their actual name.
A collection is an object that contains a group of related objects.
A collection class is a VBA class that defines a Private Collection object and implements methods to add, remove, retrieve and count objects in the collections.

Why use a Collection ?

The Collection object provides a convenient way to refer to a related group of items as a single object.
The concept of a collection makes it easy to work with all related objects at once.
The items, or members, in a collection need only be related by the fact that they exist in the collection.
Members of a collection don't have to share the same data type.

What can a Collection contain ?

Basically anything. Variables, fields, object references and even other collection objects
When you create an object for an application you may find that the relationship between two objects is one-to-many.
These are objects themselves that contain other objects that are closely related.
A collection is a group of objects of the same class.
If you supply a key to a collection member then each key must be unique
You will get a run time error if you attempt to add a new member with a key that already exists.
To refer to a member of a collection, you can refer to it by its position in the collection as an index or by its name, as a text string.

Collections can be either 0-based or 1-based.
The majority of them are 1-based although not all of them.
Using a for-each loop to iterate through a collection or array is faster than an indexed loop
The name of a collection object is typically the plural of the type of object it contains.
For example Workbooks is a collection of Workbook objects.
Collections typically have a standard set of properties and methods (Item, Count).
There are several ways you can iterate through a collection (for, foreach)
Collections in the office object model are generally always 1-based, meaning that they start with an index of 1 and not 0.
The Item method can generally always accept either a numeric value or the name of a specific object.
You should then iterate through this list or array and delete them.

Iterating through a collection of COM objects
The correct way is to get the count and use a for loop and index
Be aware that the index accessors are not consistent
Most objects are 1-based but some are 0-based

Members can be added before or after an existing member based on both the index number or a key value
Members can be retrieved and deleted using both the index number or a key value
Multiple deletion using the index number should be done in descending order because the collection is reindexed every time.
An error will occur if a unique string is used that matches any of the existing keys.

Create a "Counting" level 5
On the level 4 collections page, add Collection Methods
Add - Adds an item to the collection allowing you to provide a key value to easy finding
Count - Returns the number of items in the collection
Item - Returns an item from the collection, either using its index number or its key value
Remove - Deletes an item from the collection, you can use either its index number or its key value

Built-in Collection Class

The problem with the built-in collection class is that you have no control over what gets added to that collection if your collection property is public in your class
The solution is to create a class that "wraps up" your collection and then provide methods and properties to emulate the Add, Remove and Item methods.
However you will loose the default Item method as well as the ability to perform a for-each loop
Instead of the for-each loop you must use Count and For-Next instead.

Custom Collection Class

To control what type of objects are placed into a collection, you must create a collection class.
Using a class gives you the ability to create custom replacements for the standard Add, Remove and Item methods.

Collection - Outside the Class

If you want to maintain a list of all the instances of a particular class then you can do this by using a Collection
More information about using collections in Standard Modules refer to Objects > Collections
This gives you immediate access to the Add, Remove, Item and Count methods

Collection - Inside a Class

It is possible to include the collection inside the actual class itself.
The advantage of adding the collection to the class means that you get much more control and you can prevent direct access to the collection from outside.
The disadvantage of adding the collection is that you cannot use the built-in Add, Count, Item and Remove methods and must write your own.
If you want to prevent direct access to the collection and create a certain amount of abstraction then you could add the Collection to the class itself.

Private colEmployees As New Collection 

Public Function Add(oEmployee As class_Employee) 
   Call colEmployees.Add(Item:=oEmployee, Key:=oEmployee.Name) 
End Function 

Public Sub Remove(vItem As Variant) 
   colEmployees.Remove vItem 
End Sub 

Public Property Get Item(vItem As Variant) As class_Employee 
   Set Item = colEmployees(vItem) 
End Property 

Public Property Get Count() As Long 
   Count = colEmployees.Count 
End Property