VBA Classes

Last Updated on February 4, 2022 – 5 min read

What are class modules? Class modules are specific VBA modules. It allows you to create customized objects with methods and properties associated. A class module can contain declarations, properties, methods, and events.

Is VBA an object-oriented language (OOP)? The creation of personalized objects organizes developments. The ability to create your classes in VBA makes it an object-oriented language as Python, C++, Java.

What is the difference between a Type and a Class? The Type statement can be useful but has restrictions. A Type is a static data structure and can’t have any functions associated. Classes modules can overcome all limitations of the statement type.

In this article, you will discover how you can use a class module in VBA and store class objects in a dictionary.

We illustrate the use of class modules through the following data about famous characters of the television show: The Simpsons. The purpose is to create a custom class to collect data about each character in a single object.

Name	Surname	Age	Country	City	Address	Quote	Phone Number	Mail	Status
Homer	Simpson	40	US	Springfield	742 Evergreen Terrace	D’oh	TBD	homer.sim@simpsons.sim	Homer Simpson lives in 742 Evergreen Terrace.
Marge	Simpson	36	US	Springfield	742 Evergreen Terrace	Homeeeer	TBD	marge.sim@simpsons.sim	Marge Simpson lives in 742 Evergreen Terrace.
Bart	Simpson	10	US	Springfield	742 Evergreen Terrace	El Barto	TBD	bart.sim@simpsons.sim	Bart Simpson lives in 742 Evergreen Terrace.
Maggie	Simpson	1	US	Springfield	742 Evergreen Terrace	Areuh	TBD	mag.sim@simpsons.sim	Maggie Simpson lives in 742 Evergreen Terrace.
Lisa	Simpson	8	US	Springfield	742 Evergreen Terrace		TBD	lisa.sim@simpsons.sim	Lisa Simpson lives in 742 Evergreen Terrace.
Moe	Szyslak	60	US	Springfield	57 Walnut Street		TBD	moe.sim@simpsons.sim	Moe Szyslak lives in 57 Walnut Street.
Milhouse	Van Houten	10	US	Springfield	TBD		TBD	milh.sim@simpsons.sim	Schoolchild

How do you create a class object in VBA?

Open the project explorer and create an object in the Class Modules folder. The title assigned to your class module will be your class name.

You can declare many Public or Private attributes in your class. You can build procedures and functions in your class module attached to your class object.

A good practice is to name your class with the letter ‘c’ in lowercase, then write the class name by starting with a capital letter.

Below the code create a Class Module named cPerson. This class module includes public attributes and a PrintDataPerson function which takes as an argument an object of the class cPerson.

' Attributes
Public Name, Surname, Age, Country, City, Address As String
Public Quote, PhoneNumber, Mail, Status As String
Public DateBirth As Date
Public Salary As Integer
 
'Function
Sub PrintDataPerson(P As cPerson)
    MsgBox P.Name & " " &  P.Surname & " lives in " & P.Address _ 
         & " and is " & P.Age & "."
End Sub

How do you use a class object in a VBA module?

In a VBA module, you can use the created class cPerson. Inside the procedure, create a variable of the class cPerson named Homer. Homer is an object of the cPerson class and has all attributes, procedures and functions declared.

Public Sub UseClassPerson()
'Use the class cPerson

    'Define an object Homer with the class cPerson
    Dim Homer As New cPerson
    
    Homer.Name = "Homer"
    Homer.Surname = "Simpsons"
    Homer.Age = 40
    Homer.Country = "US"
    Homer.City = "Springfield"
    Homer.Address = "742 Evergreen Terrace"
    Homer.Quote = "D'oh"
    Homer.PhoneNumber = "TBD"
    Homer.Mail = "homer.sim@simpsons.com"
    Homer.Status = "Works at nuclear power plant"
    
    'Use the function associated in the class cPerson for Homer object
    Homer.PrintDataPerson Homer
    
End Sub

Store several objects from the same class in a dictionary

Handling one class object is helpful, but several are better. The idea is to store several classes in a collection of objects.

As seen in the following article dictionary items, the best method in VBA to call an object is to use a dictionary by storing key-item pairs. You can store a key with a class object as an item.

How to create your dictionary with a class object as an item? You need to loop once on your data and create class objects to store information of characters in a dictionary.

Each class object will be stored in a dictionary with the person’s name as the key to respect the uniqueness and a class object cPerson as an item.

As a reminder, the class module to create is the following :

Public Name, Surname, Age, Country, City, Address As String
Public Quote, PhoneNumber, Mail, Status As String
Public DateBirth As Date
Public Salary As Integer
 
Sub PrintDataPerson(P As cPerson)
    MsgBox P.Name & " " &  P.Surname & " lives in " & P.Address _ 
         & " and is " & P.Age & "."
End Sub

In your VBA module, you can declare variables outside procedures to use these variables in each procedure once initialized.

In the StockDataPerson procedure, first, call the RetrieveColData procedure. It allows you to store the column number of each field. Then loop on data and create the key with an object of class cPerson. For each new object created, use the following syntax Set Person = New cPerson. It resets the last variable to a new object of the class.

The code to stock class objects in a dictionary is the following :

'Declarations of variables outside a procedure 
'so that they can be used anywhere in the project

Public DicoPerson As Object

Public ColName, ColSurname, ColAge As Integer
Public ColCountry, ColCity, ColAddress As Integer
Public ColQuote ,ColPhoneNumber, ColMail, ColStatus As Integer

Sub RetrieveColData () 
'Procedure to find the column numbers

   Sheets("Data").Activate
   For jCol = 1 To 10 
      If Cells(1,jCol).Value2 = "Name" Then ColName=jCol
      If Cells(1,jCol).Value2 = "Surname" Then ColSurname =jCol
      If Cells(1,jCol).Value2 = "Age" Then ColAge =jCol
      If Cells(1,jCol).Value2 = "Country" Then ColCountry =jCol
      If Cells(1,jCol).Value2 = "City" Then ColCity =jCol
      If Cells(1,jCol).Value2 = "Address" Then ColAddress =jCol
      If Cells(1,jCol).Value2 = "Quote" Then ColQuote =jCol
      If Cells(1,jCol).Value2 = "Phone Number" Then ColPhoneNumber =jCol
      If Cells(1,jCol).Value2 = "Mail" Then ColMail =jCol
      If Cells(1,jCol).Value2 = "Status" Then ColStatus =jCol
   Next jCol

End Sub 

Sub StockDataPerson ()
'Procedure that stores cPerson class data in a dictionary

    Dim NbRows As Integer

    Set DicoPerson = Nothing
    Set DicoPerson = CreateObject("Scripting.Dictionary")

    Call RetrieveColData

    NbRows = 11

    For iRow = 2 To NbRows 
        Key = Cells(iRow,ColName).Value2
        If Not DicoPerson.Exists(Key) Then 
            Set Person = New cPerson
            Person.Name = Cells(iRow,ColName).Value2
            Person.Surname = Cells(iRow, ColSurname).Value2
            Person.Age = Cells(iRow, ColAge).Value2
            Person.Country = Cells(iRow, ColCountry).Value2
            Person.City = Cells(iRow, ColCity).Value2
            Person.Address = Cells(iRow, ColAddress).Value2
            Person.Quote = Cells(iRow, ColQuote).Value2
            Person.PhoneNumber = Cells(iRow, ColPhoneNumber).Value2
            Person.Mail = Cells(iRow, ColMail).Value2
            Person.Status = Cells(iRow, ColStatus).Value2
            DicoPerson.Add Key, Person
        End If
    Next iRow

End sub

This method makes it is easier to access items with the key in the dictionary without looping through data.

If you need to get Marge’s age, use the key Marge in the dictionary and call the item attribute associated.

Sub CallAttribute() 
'Call attributes of an object stored in the dictionary

    If DicoPerson("Marge").exists Then
        MsgBox DicoPerson.Item("Marge").Name & DicoPerson.Item("Marge").Surname & _
            " is " & DicoPerson.Item("Marge").Age & " lives in " & _
                DicoPerson.Item("Marge").City & "."
    End If

End Sub

Increment an item attribute of a class object stored in a dictionary

Now, the purpose is to have the sum of ages of all characters with the surname Simpsons.

You can create by browsing on data the objects of class cFamily. The dictionary key will be the family name. Items will be class objects with three attributes: the age sum of each member, the first name, and the concatenation of email addresses.

First, create the cFamily class module with the following attributes :

Public Name, Surname, Mail  As String
Public Age  As Variant

Now, browse on data, and separate the loop into two cases:

– if the object does not exist in the dictionary, you can create it;

– if the class object has already been created and added as an item to a dictionary key, you can concatenate first name, email address and sum the ages of the existing items.

Sub StockDataPerson ()
'Procedure that stores cFamily class data in a dictionary

    Dim NbRows As Integer
    Dim Surname As String

    Set DicoFamily = Nothing
    Set DicoFamily = CreateObject("Scripting.Dictionary")

    Call RetrieveColData 'To retrieve the number of each field in the dataset

    NbRows = 11
   
    'Loop on the dataset Simpsons
    For iRow = 2 To NbRows 
        Surname = Cells(iRow, ColSurname).Value2
        If Not DicoFamily.Exists(Surname) Then 
            Set Person = New cPerson
            Person.Name = Cells(iRow,ColName).Value2
            Person.Surname = Cells(iRow, ColSurname).Value2
            Person.Age = Cells(iRow, ColAge).Value2
            Person.Mail = Cells(iRow, ColMail).Value2
            DicoFamily.Add Key, Person
        If DicoFamily.Exists(Surname) Then 
            DicoFamily.Item(Surname).Age = DicoFamily.Item(Surname).Age + Cells(iRow, ColAge).Value2
            DicoFamily.Item(Surname).Name = DicoFamily.Item(Surname).Name & " - " & Cells(iRow,ColName).Value2
            DicoFamily.Item(Surname).Mail = DicoFamily.Item(Surname).Mail & " - " & Cells(iRow, ColMail).Value2
        End If
    Next iRow

End Sub

The dictionary contains the following data :

Key	Item.Name	Item.Age	Item.mail
Simpsons	Homer – Marge – Bart – Maggie – Lisa	95	homer.sim@simpsons.sim – marge.sim@simpsons.sim – bart.sim@simpsons.sim – mag.sim@simpsons.sim – lisa.sim@simpsons.sim
Szyslak	Moe	60	moe.sim@simpsons.sim
Van Houten	Milhouse	10	milh.sim@simpsons.sim

You learn in this article to create and use a class module in VBA. And, most important to process data and store several class objects in a dictionary with keys and classes objects as items!

How do you create a class object in VBA?

How do you use a class object in a VBA module?

Store several objects from the same class in a dictionary

Increment an item attribute of a class object stored in a dictionary

Share this:

Leave a Reply