VBA Class Module

Excel VBA Class Module

Class module in VBA can be defined as the module that helps to create your own objects with your own properties and methods like worksheets and range objectives of excel.

In simple terms with the help VBA class module, we can create objects with own properties. Before going to create a class module and its objects in VBA it is advisable to understand the concept of class and object. Consider a real-world example to understand what is a class and what is an object and its relation.

For example, if we take a building plan or blueprint of a building, with the help of a building plan we can construct not only one building but we can use the same plan and can construct multiple buildings in different places. They may vary in size of rooms or height of roof etc. but the actual properties of the building remain the same as there should be a basement, doors, pillars, ceiling, etc.

Similarly, if you take a car blueprint or model we can produce multiple duplicates to it. A car will have properties like a number of wheels, color, power, etc. some of the properties may vary from car to car, but the main configuration will remain the same.

To make it clearer we can define like:

• Class: It is a blueprint of a thing which helps to create an object.
• Object: Actual existence of a thing.

How to Insert a Class Module in Excel VBA?

Below are the different examples to insert a class module in excel using VBA code.

Excel VBA Class Module – Example #1

Below are the different steps to insert the class module using VBA code in excel.

Step 1: Go to the “Developer” menu on the top and click on the Visual Basic in the left-hand side.

Step 2: To create a class module we need to insert a class module in VBA. Click on the Insert drop-down on the top. From that choose a Class Module.

Now a class module is inserted on the left side in VBA as shown in the below screenshot.

As discussed earlier a class module is a blueprint with which we will create objects. So, we need to define a few variables and methods which will be accessible to all objects.

Step 3: Change the class module name as per your requirement, here I changed as Blueprint

Step 4: Now we need to define the variables and methods.

Code:

Option Explicit

Public add As Integer
Public pro As Integer
Public x As Integer
Public y As Integer

As shown in the above screenshot first start with the option explicit. Why option explicit? It will throw an error if we use any variable in the program that is not defined so, we should define all variables explicitly. Here we define four variables add, pro, x, and y. All variables are defined as public because then only these variables can be accessed by other modules.

Step 5: Up to now, we defined variables now need to define methods or functions.

Code:

Option Explicit

Public add As Integer
Public pro As Integer
Public x As Integer
Public y As Integer

Sub sum()

add = x + y
MsgBox (add)

End Sub

I defined one simple method sum(), you can define complex subprograms as per requirement. In sum() sub-method we defined how to perform addition operation and where to store that value and then finally display the result in a message box.

Step 6: A class module has been created now it’s time to create a normal module to use the class blueprint. Hence insert a module in the same way how we inserted a class module.

Step 7: By default, the module name will be module1 we can change if we want. Write the code in module1.

Code:

Sub math()

Dim obj As New Blueprint
obj.x = 5
obj.y = 6
obj.sum

End Sub

Here we are writing a subprogram math(). In that, we created an object with name obj as same as a class blueprint. That is the reason we have defined in such a way “obj as new blueprint” which means duplication of blueprint and the name is obj.

In this program, we did not define x and y but directly assigning the values to x and y because these are already defined in a class module and the obj is duplication of it. Hence it will have those variables, but to call them we should use an object.variable name.

Obj.x = 5 mean, value 5 will be stored in x variable.

Obj.y =6 mean value 6 will be stored in y variable.

Now call the methods that defined in the class module by using object name obj.

Obj.sum() will perform the addition operation as defined in the class module. In the class module, we defined as x+y and an object, we defined the values for x and y hence it will take the values and will perform the calculation and store the result in add which is an integer type. In the last step, it will display the result in the message box.

Step 8: Run the module1 by pressing the F5 key or by clicking on Play Button.

Step 9: Select the macro name and click on the Run option.

Results will be displayed as below.

Excel VBA Class Module – Example #2

Follow the below steps:

Step 1: Now add another method called “mul” to perform multiplication operation. The result should be stored in the cells(5,5) of sheet1.

Code:

Sub mul()

pro = x * y
Sheet1.Cells(5, 5) = pro

End Sub

In the above mul() method we multiplied x and y, and the result is stored in “pro” which is an integer data type.

Step 2: Now, go to module1 and call this mul method using the object obj as below.

Code:

Sub math1()

Dim obj As New Blueprint
obj.x = 5
obj.y = 6
obj.mul

End Sub

Step 3: Press F5 Key to run the module. Go to sheet1 and check the result will be displayed as below.

In the previous example, we displayed results in the message box but in the second method, we displayed in excel. So, if we declare the variables and methods in the class module we can use it in other modules with the help of an object without repeating the code. Hope you have understood how to insert the class module in Excel VBA and how to create an object for it.

Things to Remember

• It is always suggestible to use option explicit while preparing a class module because there may be typo error while using variables in VBA.
• If we do not mention sheet number for displaying results it will pick the active sheet automatically.
• Use Public Keyword while initiating the variables so that the variables can access by other modules.
• You can call the variables or methods only after creating the object. hence, create an object for class then call the variables and methods.

Recommended Articles

This is a guide to VBA Class Module. Here we discuss how to Insert Class Module in Excel using VBA code along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Find and Replace
2. Excel Sum by Color
3. VBA Check File Exists
4. Excel Delete Pivot Table
5. VBA Code | Examples