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:
Create a new class that provides at least one method that returns a
Collection
object.Provide a method in the class that allows others to add items to that collection.
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 ...
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.