Custom Collection Classes

This page how to create a custom Collection Class.

Introduction

In intermediate and advanced level programming in VBA, you often use classes to encapsulate data and the functions that operate on that data. Additionally, you will often create multiple objects of a single class. To prevent one instance of a class from being destroyed when you set the object variable to a new instance, you may want to store those object variables in a Collection object. If your project or module is entirely private and no one else will be using it, you can safely store the objects directly in a Collection. However, if you are writing code that will be used by other projects, either by inclusion or referencing, or if you are writing a component or add-in in VBA, you probably don't want to expose the Collection to the outside world. If you do, you have no control over what is added to the Collection and thus loops through Collection may throw an error due to an unexpected Type stored in a Collection.

The solution, which provides type safety while still having the indexing and looping features of a Collection, is to create a custom Collection class that provides type checking and, perhaps, additional functionality beyond what is possible with a standard Collection. On this page, we will work with two classes. The first class is named CFile and represents a single file. The second is named CFiles and provides a Collection-like object for storing a number of CFile objects. Behind the scenes, CFiles manages a regular Collection object but restricts the type of item that can be added to only CFile objects. The CFiles object also provides additional functions beyond those available from a Collection.

The CFile Class

For illustration, we can make the CFile class quite simple. Insert a new Class Module into your project, and give it the name CFile. If this class needs to be visible by other projects, change the Instancing property to 2 - PublicNotCreatable. Paste the following code into the CFile class.

Option Explicit
Option Compare Text

Private pFullName As String
Private pExists As String
Private pLastModifiedDate As Date

Public Property Get FullName() As String
    ' read-write
    FullName = pFullName
End Property

Public Property Let FullName(value As String)
    ' read-write
    pFullName = value
End Property

Public Property Get Exists() As Boolean
    ' read-only
    Exists = (Dir(pFullName) <> vbNullString)
End Property

Public Property Get LastModifiedDate() As Date
    ' read-only
    If Me.Exists() = True Then
        LastModifiedDate = FileDateTime(Me.FullName)
    Else
        LastModifiedDate = 0
    End If
End Property

This defines three simple methods: FullName, Exists, and LastModifiedDate. The FullName property is read-write and the other two are read-only. The class CFiles will be written to hold multiple CFile objects.

The CFiles Class

To get started, insert a new class module to the project and name that class CFiles. If this class needs to be visible to other projects, set the Instancing property to 2 - PublicNotCreatable. This class will provide methods that do the same thing and have the same names as methods of a generic Collection object. The members of CFiles wrap up the functionality of a Collection object that is declared Private within the class. The following is the declarations section of the CFiles class:

    Private Coll As Collection
    Private CollKeys As Collection

Here, Coll is a Collection object in which we will store multiple CFile objects. The CollKeys Collection is used to store the keys of the CFile objects stored in the Coll Collection. We need this second Collection because the Keys of a Collection are write-only -- there is no way to get a list of existing Keys of a Collection. One of the enhancements provided by CFiles is the ability to retrieve a list of Keys for the Collection.

Next, we write the Class Initialize and Terminate methods. The Initialize member initializes the two Collections, and the Terminate member destroys those Collections:

    Private Sub Class_Initialize()
        Set Coll = New Collection
        Set CollKeys = New Collection
    End Sub
    
    Private Sub Class_Terminate()
        Set Coll = Nothing
        Set CollKeys = Nothing
    End Sub

Strictly speaking, it is not necessary to set the Collection objects to Nothing in the Terminate member -- VBA will do this automatically.

The Two Types Of The Add Method

There are two ways you can implement an Add method of the CFiles class. This first is to allow the calling code to create a new object and then pass that to the Add method. The second method is to write the Add method so that it creates the new object itself. The first method is more common with generic Collection objects. Indeed, there is no way within the confines of a Collection to create an object with the Add method. For example,

    Dim Coll As New Collection
    Dim Obj As YourObjectType
    Set Obj = New YourObjectType
    Coll.Add Obj

However, with many of Excel's built-in collections, such as the Worksheets object, the only way to create an object is to create it with the collection's Add method. For example,

    Dim WS As Worksheet
    Set WS = Worksheets.Add()

With a worksheet (as well as other objects), you cannot directly create an instance of the class -- it must be created via the Add method of the collection.

Neither method is particularly better than the other. Which method to use depends on both personal style and the overall design of your application. If you choose the second method, in which the Add method creates the object, the Add method should accept the proper parameters so that it can fully create the new object with the need for additional code to make the object "complete". For example,

    Set MyNewObject = MyCollection.Add(Name:="TheName", Key:="TheKey", SomeValue:=123, AnotherValue:=456)
    ' rather than 
    Set MyNewObject = MyCollection.Add()
    MyNewObject.Name = "TheName"
    MyNewObject.SomeValue = 123
    MyNewObject.AnotherValue = 456

This page last updated: 2-May-2008