WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Class Modules

VBA Class Modules

Excel VBA Class Modules

When we use VBA we use the properties and attributes defined in VBA but what happens when we want to create our own properties and methods and attributes, that is when we use a class module in VBA so that we can have it user-defined, a class module has its own set of codes defined for functions, properties, and objects by the user.

Class Modules are used to create an object. When we say items, even though it is a variable, those are small programs. While writing the code, we usually write in modules. Essential modules are where we write our principles to do the job. We also use User Form to create Graphic User Interfaces.

VBA Class step 1

But if you look at the above image, you can see “Class Module.” I know for sure you have not touched that until you are reading this post. You must be wondering what is this VBA class module is when all the job can be done by using our regular Module itself.

What is the Class Module?

Class Modules allow a user to create their object just like how we have built-in items in standard modules like “Worksheets,” “Workbooks,” “Range,” and so on.

Like this using a class module, we can create custom objects.

The class has a direct relationship with objects. For example, you have a machine diagram to build a machine but remember it is not a machine yet and using this machine diagram, and we can make many machines like that.

For example, if you want to list various machine brands to list the characteristics of multiple models.

In the machine, we have a brand name, series number, machine power, color of the device, number of motors involved in it, motor fuel type, etc.… In technical language, these are called “properties.”

Concerning the properties of the machine, we can start, we can turn off, we can increase the speed of the motor, we can pause, etc… And these are called “Methods.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example

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

Let’s start the ball rolling because it is always the boring thing to read out the theoretical part. To insert a class module, go to the insert option in a basic visual editor.

VBA Class step 1

Now we can see a class module like the below.

VBA Class step 2

This looks similar to the one we have above as a regular module. Change the name of the class module in the properties window. To see the properties window, press the F4 key.

VBA Class step 3

Now declare the variable as a string.

VBA Class step 4

Without creating a subprocedure in VBA, we need to declare the variable and use the word “Public” not “Dim.”

Now we can access this variable in any module and class module.

Now go to a regular module and name the variable.

VBA Class step 5

After declaring the variable, we need to assign the data type in VBA; instead of setting the data type, we can give the name of the class module, i.e., CM.

VBA Class step 6

Using the variable “k,” we can access the public variable we have defined in the class module, i.e., “My Value.”

step 7

As we can see in the above picture, it is showing the option of variable name from the class module to assign the value to it.

step 8

Now show the value of the assigned variable in the VBA message box.

Code:

Sub Class_Example()

  Dim k As New CM

  k.MyValue = "Hello"

  MsgBox k.MyValue

End Sub

step 9

Run this code using the F5 key or manually to show the result.

step 10

Class Module vs. Objects

At the initial stage of the class module, everybody gets confused with what class is and what an object is.

To understand this, recollect our earlier example of a machine diagram. The first thing we need to produce a machine is we need to design the machine first, and then several copies can be replicated with that design.

Now relate this to our class module.

  • Here Class Module is a Design. And Object is the copy created by the Design.
  • One more interesting thing is we need to use the word “new” to create an object from the class module.

Below is an example of the same.

step 11

One more thing when we use built-in objects like worksheets, workbooks, and range objects, we don’t use the word “new.”

To start off the proceedings with the Class Module, these basic things you should know. In the coming articles, we will see the next level examples.

It seems difficult to understand this; the more time you spend with a class module, you will get used to it.

Recommended Articles

This has been a guide to VBA Class Module. Here we learn how to create and use a class module in VBA and also the difference between the class module and object along with examples & downloadable excel templates. Below are some useful articles related to Excel VBA –

  • Pause VBA Code
  • List of VBA Worksheet Function
  • VBA On Error Methods
  • VBA IsEmpty
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Class Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More