EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Coding VBA Class Module
 

VBA Class Module

Madhuri Thakur
Article byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

You can download this VBA Class Module Excel Template here – VBA Class Module Excel Template

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.

VBA Class Module Example 1-1

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.

VBA Class Module Example 1-2

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

VBA Class Module Example 1-3

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

Module Name Example 1-4

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

VBA Class Module Example 1-5

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

Define Methods and Function Example 1-6

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.

Insert Module Example 1-7

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

VBA Class Module Example 1-8

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.

Run the Code Example 1-9

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

VBA Class Module Example 1-10

Results will be displayed as below.

VBA Class Module Example 1-11

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

VBA Class Module Example 2-1

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

VBA Class Module Example 2-2

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

VBA Class Module Example 2-3

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. VBA Check File Exists
  3. VBA Code
  4. VBA RGB

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Class Module Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW