Class modules make VBA as truly object oriented programming language, like the other ones as Java or C++. At least a little bit. They are the way for You to create your own objects in the code. In this article I’m going to show You how You can build new objects using class modules in VBA.
How to create class module in object explorer?
To create class module You need to right click on the Project Explorer window, which is usually visible by default, choose Insert and Class Module.
After that You add new class module to the VBA project, You can change its name. Usually in front of the name I use cls to not get wrong using IntelliSense, for example clsPerson.
There is also Instancing set by default to 1-Private, which is telling us that it will be available in this project. If You want to make a possibility to use this class in other projects , change this option to 2-PublicNotCreatable.
Class variables
The easiest way to declare variables of your class it to declare them Public.
Option Explicit
Public ID As String
Public Name As String
Public Age As String
Public City As String
You can also approach a Get & Let/Set variables method, but its way more complex philosophy and in most cases it is enough to just use Public keyword.
How to declare and access object of class type?
As I mentioned in the beginning, class modules are the objects creating by yourself, so they are declared in the same way as objects. You can do it in the short or longer way, often depends on the situation.
Dim person As clsPerson
Set person = New clsPerson
'or
Dim person As New clsPerson
Longer way is very convenient approach in case of loops and collections.
If You already declared and set new object of your class, You can easily preview – using IntelliSense – what are the available options. I mean methods and properties of class.
In this case there are 4 properties of clsPerson class object available – Age, City, ID and Name.
How to use class module in standard code?
One of my favorite use example of class modules is building collection of class objects using loop.
Sub classModuleExample()
Dim wb As Workbook, ws As Worksheet, lastRow As Long, i As Long
Dim col As Collection, person As clsPerson
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
Set col = New Collection
Let’s build a loop, from the second row to the last row, and create a new class object for every iteration. During that gather all the information from columns – ID, Name, Age & City and filled with data object add to the collection.
With ws
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
Set person = New clsPerson
person.ID = .Cells(i, 1).Value
person.Name = .Cells(i, 2).Value
person.Age = .Cells(i, 3).Value
person.City = .Cells(i, 4).Value
col.Add person
Next i
End With
In the end You created collection of class objects, with which You can do whatever is needed.
Class modules improves your code
Looping through such collection is much faster than through worksheet cells. The bigger data is the faster this approach works. Also class modules are making code easier to modify in future, compared to use of standard variables.
What is also important understanding such approach of coding makes You more open to world of object oriented programming and other OOP languages. If You want to improve your skills it is highly recommended to at least understand the purpose of the classes.