VBA Class Modules – Create Objects

VBA is an Object Oriented language. It contains the use of various Objects, their functions, and properties like Worksheets. Today, we will learn to create our own Objects in VBA with the help of Class Modules.

So let us start learning.

What are Class Modules?

Using a Class Module, we can define a Class. A Class acts like a blueprint to create any number of Objects on the base of that Class. The methods and properties are defined inside the class definition inside Class Module.

Let us consider a real-life example of a Class and an Object. If we have a recipe for a Dish, then we can use it to cook the Dish multiple times. The Dish will have ingredients, the time required to prepare, number of people it can serve as its properties. Whereas. there can be different things that we can do with it including eating, rating, cooking, etc. These are the methods.

Here, the recipe of the dish is like a Class where the prepared Dish is the Object. An Object has actual existence in our program.

• Class – It is a blueprint where we define the properties and methods.
• Object – This is an entity created with the Class having actual existence.

Inserting a Class Module

Now we will learn how to insert a Class Module in VBA.

• Go to the Developer tab on the ribbon and click on the Visual Basic button.

• This opens the VBE. Go to the Insert tab and click on Class Module.

• As a result, this inserts a new class module as follows.

We can create Objects from Class Modules.

Syntax to Define a Class in Class Module

The following is the syntax to define object variables and procedures in VBA.

'define a variable
Private/Public <variable_name> As <Data_Type>

'define a method
[Private/Public] Sub <sub_name>()
'code
End Sub

A variable can be declared in two ways.

• Public – A Public variable can be accessed by an Object in any part of our program with the help of the dot operator.
• Private – A Private variable can be accessed only within the Class Module methods.

Methods are Public by default. So when we do not specify the scope of the method, it is Public. We can call Private methods only from within the Class Module.

We can make an Object from the Class as follows.

Dim <object_name> As New <class_name>

Example to Create Class and its Objects

In this example, we will learn to create a Class and then make its Objects. We will defined methods and properties in the class and then use the dot operator to access them.

Follow these steps to start implementing.

• Insert a Class Module in VBA project.
• Go to View Tab in VBE and click on Properties Window to open it. Alternatively, you can use the shortcut key F4 to open the Properties Window.
• Click on the Class1 Module in the Project Explorer and change its name from Properties Window to “BluePrint”.

• The Name of the Class is now BluePrint. Open this Class Module in VBE and declare two public variables of Integer type.

• Now we will write a Sub Procedure named Getter to print the values contained in the Object variables. We will define another Sub Procedure that prints the sum of two numbers.

• Insert a new Module from the Insert tab.

• Enter the following Sub Procedure code in the new Module1.

Sub exCreateObjectOfBluePrint()
'make new object named as obj_blueprint of the class BluePrint
Dim obj_blueprint As New BluePrint
'initialize the value of public object variables
obj_blueprint.num1 = 10
obj_blueprint.num2 = 20
'print these values using Getter method
obj_blueprint.Getter
'print the sum of two numbers using sum method
obj_blueprint.Sum
End Sub

Now, when you run this Sub Procedure,

• A new Object obj_blueprint of type BluePrint is created.
• We initialize object variables of obj_blueprint.
• Method Getter is called.
• Method Sum is called.

This brings us to an end.

Thank you for reading.