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 in VBA, 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 in VBA

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

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.

In this article, we will discuss the ways of using a VBA class module. Read on…

What is the Class Module in Excel VBA?

VBA Class Modules are allowed a user to create their own object just like how we have built-in objects in regular modules like “Worksheets”, “Workbooks”, “Range”, and so on.

Like this using Class Module, we can create custom objects. So in VBA Class Modules are used to create our own 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 we can build many machines like that.

Class Modules are used to create an object in VBA. When we say objects even though it a variable those are small programs.

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

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

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

How to Create a Class Module in Excel VBA

We will show you a simple example of using the Class function in VBA.

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

Now we can see a class module like the below.

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

Now declare the variable as a string.

Without creating a sub procedure we need to declare the variable and also this time using 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.

After declaring the variable we need to assign the data type, instead of assigning the data type we can just give the name of the class module i.e. CM

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

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.

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

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

Class Module vs Objects

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

To understand this recollect our earlier example of 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 VBA class module in Excel. Below is an example of the same.

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 Class Module, these basic things you should know. In 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. Here we learn how to create and use Class Module in Excel VBA and also the difference between the class module and object along with examples & downloadable templates. Below are some useful articles related to Excel VBA –

• VBA FileCopy Function Examples
• Create an Instance of VBA FileSystemObject
• How to Pause VBA Code from Running?
• Excel Examples to Convert String in VBA
• List of VBA Worksheet Function
• VBA UBOUND Function
• VBA On Error Methods
• VBA IsEmpty Function