VBA Class Modules and How to Use Them

What is a Class in VBA?

A class is a blueprint for an object to be created. A class itself does nothing but using classes you can create multiple identical objects that can perform operations or can be used as a data resource.

In real life, the model (design and functionalities) of a car is a class and the car itself is an object of that class. In the class, we define what the car has in it and what it can do. For example, a car has 4 wheels, 5 gears, steering wheel, etc. These are attributes/properties. We also define what a car can do, like moving forward, backward, turn, etc. These are functions of the car class. A car object created using car class, will have all these properties. If you define a car that has 5 wheels than a car created using this class will have 5 wheels. You get the point.

Enough of theory, now let's see how you can use a class module in VBA.

Using Excel VBA class module

While working in VBA, you must have used Range("A1").select. The Range is pre-defined class in VBA. The select is one of the functions of Range class that select the specified range. Similarly, Debug is class in VBA and print and assert are it's methods. Worksheets, Workbooks, Range, etc. all are VBA classes that we use in our subs.

Create your own class

First, we need to add a class module in VBA

Press ALT+F11 key combination to open excel VBA editor.

- Right-click on project explorer. Move the cursor to Insert--> Class module. Click on it. The same thing can be done from the Insert menu.

- The class will be added to the folder "Class module". The default name is like class1, class2, and so on. You can change the class name from the property window. Let's name our class "Welcome".

Now let's create add some attributes to our class. Since I want these attributes to be available to the public, I have use accessibility operator public.
```
Public name As String
Public var1 As Integer
Public var2 As Integer
```
Now let's add a function to this class. I want a function that says Hi! to the user. To do so add a sub and name it sayHiTo.
```
Sub sayHiTo(user As String)
    name = user
    MsgBox ("Hi! " & name)
End Sub
```

Now let's use class in a module. Insert a new module if you don't have any. Write a sub. I have named my sub Test.

Sub test()
 Dim wc As New Welcome 'Declared and initialized Welcome object
 wc.sayHiTo ("Jack") 'used sayHiTo method of Welcome Object.
End Sub

Run this sub Test using F5 key. It will prompt "Hi! Jack" on excel workbook.

How does it work?

In sub Test, we have created is an object "wc" of Welcome class. An object is created in VBA in two methods. we run the code, Test sub creates an object wc of the Welcome class. This object has all the properties of the Welcome class. We use the sayHiTo method of the Welcome class to say hi to the user.

Object Creation in Excel VBA

1. Instant creation

In Instant creation, we create an object while declaring the object with the "new" key. In our example above, we have used instant creation.

Dim wc As New Welcome

2. Delayed creation
In delayed creation, we first declare the object only. We don't use the "new" keyword. In order to use the object, we need to initialize it with the "new" keyword.

Sub test()
 Dim wc As Welcome
 'wc.sayHiTo ("Jack") 'generates error since wc is not initialised yet
'initialising object
Set wc = New Welcome
wc.sayHiTo ("Cory") 'this will work.
End Sub

Accessing Variables of a Class

In the above examples, we have used public variables for class but it is wrong to practice. We should avoid using public variables in a class. Now the question is how would we access variables of the class. Earlier, we used subroutine to access name but VBA classes provide properties that are used to systematically update and retrieve private variable values of the class. The properties are more elegant than sub or function for updating and accessing private variables. Let's see how.

Syntax of class property

Private name As String
Private var1 As Integer
Private var2 As Integer
Property Let MyName(nm As String)
name = nm
End Property

Property Get MyName() As String
MyName = name
End Property

Let's use them in a module.

Sub test()
 'creating class object
 Dim wc As New Welcome
 Dim wc1 As New Welcome
 
 'using properties
 wc.MyName = "Exceltip.com"
 wc1.MyName = "ExcelForum.com"
 
 Debug.Print wc.MyName
 Debug.Print wc1.MyName
 
 End Sub

When you will run this test sub, you will get two names printed for two objects of the "Welcome" class.

How properties are different from sub and functions

In the above example, notice that we have used MyName property as a variable. We initialized the value of "Name" variable just by writing wc.MyName="assdf". This line of command called the property called Property Get MyName() As String. We didn't pass any value in parenthesis as we did in the beginning.

Similarly, to print the values of "Name" variable we used command Debug.Print wc.MyName. Isn't it as simple as normal variable initialization? The only difference is that you can do a whole lot in the property segment. You put data validation, calculation, communication, etc. and user will only see the result.

Another difference is that we can use same name of the property to let and get part. This makes it easier and less confusing.

So yeah guys, this was a simple example of a class module in Excel VBA. This is just the tip of the iceberg, there's a lot of juice in this topic that we will explore in later articles. We will explore each of them one by one in the easiest way possible. I hope I was explanatory enough to make you understand this. If you have any doubts regarding this topic or any other excel VBA topic, mention it in the comments section below.

Related Articles:

Import a module from a file using VBA in Microsoft Excel | Learn how to import entire module from another file using VBA.

Create a new module using VBA in Microsoft Excel | You can use a module to create another model in VBA. This can help you minimize the additional overhead work.

Add a procedure to a module using VBA in Microsoft Excel | To add procedures to modules automatically use this VBA code.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.