Return to VBA Code Examples

VBA Class Constructor

This tutorial will teach you about the Class Constructor in Class Modules in VBA.

When you create a Class Module in VBA, you can create a class Constructor that will run when the class is called.  This Constructor is called the Initialize Event in VBA.

Initialize Event in a Class Module

Consider the worksheet below:

VBAConstructor Worksheet

In the VBA code, we can add a class module called clsEmployees to the VBA Project, and then we can create an Initialize Event which can set the Range object as the Range (“B2:D11”) in the Employees Sheet.

In a standard module, we can then call this class and we are then able to run through all the details contained in the range.

VBAConstructor Code Example

In the example above, 1) the EmployeeDetails sub-procedure is created within a new module. It creates a new instance of clsEmployees. In doing so, the initialize event in the class is fired, which in turn sets the Range object.

We can then 2) loop through the Range Object using the For Each…Next Loop to Debug.Print each value in the Range as shown in the immediate window below.

Initialize Event in a User Form

Userforms are essentially class modules.  Therefore they too have an initialize event and therefore a Class Constructor.

Take the following form for example.  It contains a ComboBox called cboEmployees.

VBAConstructor UserForm

In the Initialize Event of this form, we can populate the combo box as follows:

VBAConstructor UserForm ComboBox

This is done using the following code:

When the form is first opened, the Initalize event (Class Constructor) will fire.  This will populate the variable rng with the Range (“B2:D12”).  We will then declare another range object (c) which will be populated when we loop through the rng.  If the column is column B (ie 2), then the name of the employee will be added to the combo list.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!