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.
|Homer||Simpson||40||US||Springfield||742 Evergreen Terrace||D’oh||TBDfirstname.lastname@example.org||Homer Simpson lives in 742 Evergreen Terrace.|
|Marge||Simpson||36||US||Springfield||742 Evergreen Terrace||Homeeeer||TBDemail@example.com||Marge Simpson lives in 742 Evergreen Terrace.|
|Bart||Simpson||10||US||Springfield||742 Evergreen Terrace||El Barto||TBDfirstname.lastname@example.org||Bart Simpson lives in 742 Evergreen Terrace.|
|Maggie||Simpson||1||US||Springfield||742 Evergreen Terrace||Areuh||TBDemail@example.com||Maggie Simpson lives in 742 Evergreen Terrace.|
|Lisa||Simpson||8||US||Springfield||742 Evergreen Terrace||TBDfirstname.lastname@example.org||Lisa Simpson lives in 742 Evergreen Terrace.|
|Moe||Szyslak||60||US||Springfield||57 Walnut Street||TBDemail@example.com||Moe Szyslak lives in 57 Walnut Street.|
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 = "firstname.lastname@example.org" 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 :
|Simpsons||Homer – Marge – Bart – Maggie – Lisaemail@example.com – firstname.lastname@example.org – |
email@example.com – firstname.lastname@example.org –
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!