If you have read my Open Letter to MS Access Users, you’d know why it is important to introduce Object Oriented Programming within your MS Access Application: because your IT and even yourself are fed up with the Mess so let’s stop it :).

Nevertheless Object Oriented Programming may be hard for people accustomed to Procedural Programming because Object Oriented Programming is Non-Linear whereas the old way is Linear or Sequential. So why would you want to complicate things ? Because of the Divide-and-Conquer law. By splitting your code into Objects, you can better maintain each component separately by concern and not mix everything like GUI Interface with your Business Logics.

So in this lesson, I’m going to introduce you VBA Class Module and I am supposing that you know at least a bit of VBA (I will surely write some basic VBA tutorial but since there are already plenties, you can probably find one without my help).

The first thing you should know is that there is a type of module which is a class module. Under MS Access 2007 you just have to click on this Icon’s Submenu:

Once inside, you can create and rename the default class1 module:

Let’s rename it to clsPerson (Best Practice in Naming Convention is to prefix each class name with cls) as we’re going to create some properties for it:

Private Id As Long
Private FirstName As String
Private LastName As String
Private BirthDate As Date

To access these properties, you need to create an object or instance first (as a class is just a template for creating objects) with the new instruction like this:

Set oPerson = New clsPerson

so that you could access an oPerson Property like this (using the Immediate Window which is a kind of VBA console):

Debug.Print oPerson.Id

Oops, something’s wrong:

Why ? Because you set the properties as Private, remember ? So let’s turn them into Public:

Public Id As Long
Public FirstName As String
Public LastName As String
Public BirthDate As Date

This time it works as the Immediate Command Window returns 0 (by default a Long Property will be inialized automatically to zero by default):

That’s fine now isn’t it … except we’ve just defeated one of the purpose of good OOP which is to protect the properties from the external world by controlling their access (like locking your house with a key to forbid people to steal your assets). So go back to private please !

But wait how are we going to access them ? Well, we’re going to create Accessors or Getters and Setters (we’ll see in the future that this also is evil but it is less evil than outright Public Properties and let’s not be too harsh for a beginning). Getters and Setters are like Doors that you must open to access the private properties and are created with Property Let / Property Get:

Private m_lId As Long
Private m_sFirstName As String
Private m_sLastName As String
Private m_dtBirthDate As Date

Public Property Get Id() As Long

    Id = m_lId

End Property

Public Property Let Id(ByVal lId As Long)

    m_lId = lId

End Property

Public Property Get FirstName() As String

    FirstName = m_sFirstName

End Property

Public Property Let FirstName(ByVal sFirstName As String)

    m_sFirstName = sFirstName

End Property

Public Property Get LastName() As String

    LastName = m_sLastName

End Property

Public Property Let LastName(ByVal sLastName As String)

    m_sLastName = sLastName

End Property

Public Property Get BirthDate() As Date

    BirthDate = m_dtBirthDate

End Property

Public Property Let BirthDate(ByVal dtBirthDate As Date)

    m_dtBirthDate = dtBirthDate

End Property

Yawn … All this for just that ? Are you kidding ? Well I know, that seems overkilled but OOP is a Paradigm shift and you need some times to get accustomed to all this. And don’t worry if it’s so verbose, I’ll show you in future lesson how you can use a nice tool to cut off this burden so that even Visual Studio Professional Coders would envy you

Good Practice, see you next !