Using Class Initialize in Collection Classes

Class Initialize is known as an intrinsic (native) procedure that triggers when a custom object is initialized. Did you know that this procedure will also work within collection classes? When used properly, class initialize can help set up collections by using default data or loading from saved data.

Note: If you came here looking for general information on using Class_Initialize in a custom object class, see the post Building a Custom Class: Part 2.


This is the third post in the Tips and Tricks with VBA Classes series. Today’s post provides an overview of using the Class_Initialize subroutine in collection classes.

Using a collection class opens two opportunities for the class initialize procedure. First, it can be used within the collection class, firing at the moment a collection is instantiated. Second, it can also be used for all objects contained within the collection, firing at the moment the collection instantiates individual objects. To summarize, if a collection class contains 5 objects, Class_Initialize can potentially fire off 6 times — once for the collection class, and 5 times for each object within the collection.

Note: The content of this post uses specific terminology for clarity. To distinguish between collection classes and normal (non-collection) classes, I introduce the term “object class” and “underlying class” to refer to the class which is used to store objects within a collection class.

Example File

This post continues to build on the example file used in previous posts of this series — a simple program which collects data on countries and stores them in classes. Remember to activate macros upon opening the document.

Class Initialize in a Collection Class

In last week’s post on Saving Custom Object Data in a Workbook, the example file provided three approaches to storing class data. This week’s example file continues the use of the “Store to Array” approach implementation to work within a class initialize procedure. For clarity, the other two approaches have been removed.

In the context of a collection class, the class initialize subroutine behaves in similar fashion to its use within normal classes. The primary difference is that—within a collection class—Class_Initialize will fire off when the collection class is created (more technically, instantiated). If you need a refresher, view the post Building a Custom Class: Part 2 for an overview of a Class Initialize implementation on classes for custom objects. When implemented within a collection class, you can force it to do something as mundane as displaying a message or changing a cell value. However, its true strength lies in its ability to build the collection.

If you recall in the previous post, we created a procedure to load data to a collection class. This load would be triggered by the user pressing a “Load” button on the spreadsheet. While this did what was needed, its implementation was not ideal — there was no guarantee that users would click the “Load” button before attempting any other operations. The procedure was independent from the class. I’ve included this procedure in the “Unused_Procedures” module for reference, and am including it here:

Old Implementation (No Longer Used)

New Implementation (Used in Class Module)

Given that the Load procedure should be the first procedure used after a class collection is created, we can automate its triggering by moving it to the class collection’s Class_Initialize subroutine. With some minor changes, it can be added to the class module as follows:

Note: Empty highlighted lines are included to show comparison (removed lines and changes) between the old and new implementation.

You will notice a few subtle differences between procedures when comparing the old (1st) implementation with the new (2nd):

  1. Lines 7 and 8 have been removed in the new implementation. In the old procedure, these were included to reset the collection in case it was already loaded. With Class_Initialize, there will only be one load.
  2. In line 12, a reference to the collection object CountryDetails has been replaced by the Me keyword. Given that Class_Initialize exists within the class it is affecting, it only needs to refer to itself. This is useful if multiple instances of the CountryCollection class are instantiated, as the previous implementation was name-specific.
  3. In line 19, the call to the Update_Validation_Cell procedure has been removed. This has been moved to the module that creates the CountryDetails object. This class will not fully initialize until it reaches the end of the Class_Initialize procedure, so it’s error-prone to include calls to external procedures which reference the collection.

Not too many changes are required in the coding modules. The CountryDetails object is set either through the Input_Form, Save_Array, or View_Country_Details procedures (through a call to the Set_Object procedure). I continue to include a load button for the user, but it only works if the collection does not exist — it’s there as a backup. The Delete_Array procedure does not attempt to set the object and will just clear the saved data from the “Data Array CountryDetail” sheet if the object does not exist.

Class Initialize in an Object Class

The class initialize procedure can also be placed inside an underlying class.

One thing to keep in mind is the precise time when the Class_Initialize subroutine fires off. You may notice in the collection class’ Insert subroutine that each individual object is initialized through the following statement.

This is the point where the object level Class_Initialize procedure will fire. Note that properties are set after this line. If you planned to use Class_Initialize to run calculations derived from the values of object properties, you’re out of luck.

In the example file, a new object-level property tracks the date the object was created or loaded. The Class_Initialize procedure is used to assign a date to the property using the Now function.

While this could just as easily been accomplished within the Insert procedure, there are two advantages of using Class_Initialize in the object class. First, it permits the use of the Me keyword. Second, if you decide to create an object independent of the collection class, the object-level Class_Initialize will still fire.

Class Terminate in a Collection Class

While the focus of this post has been class initialize procedures, it’s also worth looking at class terminate. This can be used to complete tasks or provide user feedback after a collection is terminated. Like class initialize, class terminate can be implemented in both the collection class and underlying class.

Collection Class

This is a simple Class_Terminate procedure which shows a message “Class Collection has been deleted” right after it is terminated.

This is triggered within the coding module by setting the class collection to nothing.

Object Class

The same can be done for the class dependent on the collection class. When a Collection is terminated, all the underlying objects will be terminated as well. As if it were within a loop, a message box will appear for each country deleted.

Message appears when an object is deleted.In the object level, the Class_Terminate procedure will also fire if individual objects within a collection are specifically targeted for deletion through the coding module.

Other Notes

  • Use caution in situations where object classes may exist both within a collection, and independently. You need to ensure objects can stand alone when being set, whether through well planned Class_Initialize procedures, or with help from procedures within the coding module to set properties.
  • Class_Initialize is an optional procedure. Likewise, you can choose to include it only within the main collection class, underlying class, both, or neither.

Leave a Reply