Class Module

Class modules are a specific type of module that allows you to create your customised objects. You can define the methods and properties associated with the class. A class module can contain declarations, properties, methods and events.

The creation of personalized objects makes it possible to better organize developments by grouping in a single object the data as well as methods for manipulation.

The ability to create your classes in VBA makes VBA an object-oriented language as in C++, Python, Java…

The Type statement as defined in a previous article useful but have some restrictions. It is not possible to declare a type of new instance. You can’t validate or restrict the values assigned to different fields. A Type is a static data structure and can’t have any actions associated. 

Use Classes Modules can overcome all limitations of the statement type. Classes are powerful without memory limitation. 

For a better understanding of the class modules, we will illustrate the use through the following dataset which represents several characters of the television show “The Simpsons”. The purpose is to create custom object to collect the values of each character in memory.

NameSurnameAgeCountryCityAddressQuotePhone NumberMailStatus
HomerSimpson40USSpringfield742 Evergreen TerraceD’oh TBDhomer.sim@simpsons.comMarge Simpson lives in 742 Evergreen Terrace and is 36.
MargeSimpson36USSpringfield742 Evergreen TerraceHomeeeerTBDmarge.sim@simpsons.comBart Simpson lives in 742 Evergreen Terrace and is 10.
BartSimpson10USSpringfield742 Evergreen TerraceEl Barto TBDbart.sim@simpsons.comMaggie Simpson lives in 742 Evergreen Terrace and is 1.
MaggieSimpson1USSpringfield742 Evergreen TerraceAreuhTBDmag.sim@simpsons.comLisa Simpson lives in 742 Evergreen Terrace and is 8.
LisaSimpson8USSpringfield742 Evergreen Terrace TBDlisa.sim@simpsons.comMoe Szyslak lives in 57 Walnut Street and is ?.
MoeSzyslak60USSpringfield57 Walnut Street TBDmoe.sim@simpsons.comCharles Montgomery Burns lives in 1000 Mammon Street and is 104.
Charles MontgomeryBurns104USSpringfield1000 Mammon StreetExcellentTBDcharles.sim@simpsons.comApu  Nahasapeemapetilon lives in TBD and is 39.
Apu Nahasapeemapetilon39USSpringfieldTBD TBDapu.sim@simpsons.comNed Flanders lives in 740 Evergreen Terrace and is 39.
NedFlanders39USSpringfield740 Evergreen Terrace TBDned.sim@simpsons.comVendor at the Palais du Gaucher
MilhouseVan Houten10USSpringfieldTBD TBDmilh.sim@simpsons.comSchoolchild

Create a new Class Module in VBA

To create a class, first, open the project explorer and create an object in the Class Modules folder. The name assigned to the class module will be the name of your class. You can declare different attributes in your class, in the same way as a type statement. Attributes are declared as Public or Private. You can also build procedures and function in the class module, which will be attached to your class object. A good practice is to start the class name by a letter ‘c’ in lowercase, then write the class name by starting with a capital letter.

Insert a class module

We will create in the example a Class Module, which we will call “cPerson”. This class module includes public attributes and a “PrintDataPerson” function which takes as argument an object of class “cPerson”.

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

Use a Class in a VBA Module

In a VBA module, we will now use the class we just created in the cPerson class module.

Inside the procedure, we will declare a variable of the class “cPerson” which we will call Homer. Homer, will be an object of the “cPerson” class and will have all the attributes, procedures and functions declared. For example, we can assign age and name to the variable Homer.

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.Status = "Works at nuclear power plant"
    'Use the function associated in the class cPerson for Homer object
    Homer.PrintDataPerson Homer
End Sub

Storage of several objects from the same class (BEST METHOD)

Handling an object is great, but several are better. The idea is to stock several classes in one object. As seen in the following article items of a dictionarythe best method in VBA to call several objects without loop is to create a dictionary that will store a key-items pair, having as an item a class object of cPerson.

How to create this dictionary object which will store all our class objects?

We will loop once on the data, and create class objects to store information of each person in memory.

Each class object will be stored in a dictionary taking as key the name and surname of the person (to respect the uniqueness of the key) and as an item the class object “cPerson”.

previously, the class module will have by the following values :

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 our module, we will declare variables outside procedures, to be able to use these variables in each procedure once initialized. In the “StockDataPerson” procedure, we will first call the “RetrieveColData” procedure that will allow us to store in memory the number of the column of each field. We will then work once on the data set and create for each new “Name – Surname” an object of class cPerson. For each new object created, we will use the following syntax “Set Person = New cPerson” that reset the variable to a new object of the “cPerson” class.

The procedure to stock class objects in the 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

   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 without using a loop. If we want to get Marge’s age, we use the Marge key in the dictionary and we call the item attribute associated.

The code to call an item will be :

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

You can buy the code of the cPerson class with the data: here.

Increment an item of the class stored in the dictionary

Presume that, now, from our previous dataset the purpose is to have the age sum of all characters having as surname “Simpsons”. We will create by browsing on our dataset a class “cFamily”, however, this one will have as key the family name and for items three attributes: the age sum of each member, the first name and the concatenation email addresses.

The first step is to create a “cFamily” class with the following attributes :

Public Name, Surname, Age, Mail  As String

We will now browse the data set, but we will have to separate two cases:

– if the object does not exist in the dictionary, we will create it;

– if the class object has already been created and added to the dictionary, we will have to concatenate the attributes first name, email addresses and sum the ages, by accessing attributes as an item of the dictionary key.

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

At the end of this procedure the dictionary will be filled with this data :

Simpsons Homer – Marge – Bart – Maggie – – – – –

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s