How to use class modules in VBA?

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.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *