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.
class modules in vba infographics

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.
inserting a class module in vba
  • This opens the VBE. Go to the Insert tab and click on Class Module.
inserting a class module in vba step 2
  • As a result, this inserts a new class module as follows.
inserting a class module in vba result

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>()
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”.
changing the name of class module in vba
  • The Name of the Class is now BluePrint. Open this Class Module in VBE and declare two public variables of Integer type.
declaring object variables in vba
  • 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.
defining object methods in vba
  • Insert a new Module from the Insert tab.
insert a new module in vba
  • 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
'print the sum of two numbers using sum method
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.
create an object in vba

This brings us to an end.

Thank you for reading.

Leave a Comment