Programming Excel with VBA and .NET

Collect Objects

Earlier I included a small procedure that converts values from a Range to a String:

    Function RangeToString(rng As Range) As String
        Dim cel As Range, result As String
        For Each cel In rng
            result = result & cel.value & ", "
        Next
        RangeToString = result
    End Function

The For Each loop in that code works because Range is a collection. A collection is a special type of object that includes a way to enumerate items contained by the object. Excel uses collections to organize its objects into a hierarchy, which is sometimes called the Excel object model . Figure 5-7 shows how collections are used to organize part of the Excel object model.

Figure 5-7. Excel uses collections to create an object hierarchy

You can create the same sort of hierarchy among your own objects by defining collections. To create a collection:

1. Create a new class that provides at least one method that returns a Collection object.

2. Provide a method in the class that allows others to add items to that collection.

3. Optionally, provide methods to remove and count items in the collection.

Most collections provide the following methods: Items, Item, Add, Remove, and Count. It’s a good idea to follow that convention unless there’s a specific reason not to enable one of those tasks. The following code shows the Messages collection, which, as the name suggests, provides a collection of Message objects:

 ' Messages ...