01 – Introduction To Class Modules

What is a class module?

A class module is a group of code that allows you to create objects and utililise them in your application.


OK, a class module is a fancy way of writing code.blueprint

Better. But why so fancy?

Writing code with class modules has its advantages but they are not always easy to see! They allow you to encapsulate your code (hide the inner workings), break your code down into bite size chunks and allow you to sound impressive in a group of Access developers!

Can’t I do all these things with normal modules?

Well yeah, kind of… But you definitely won’t sound impressive in a group full of Access programmers. Anyway, why don’t we take a look at what we are talking about.


Standard vs. Class Modules

Let’s use a simple example to demonstrate the difference between standard and class modules. Let us say you want to write a function that works out someone’s age after you have supplied their date of birth.

Here is a function to work out someone’s date of birth (courtesy of Allen Browne):

Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Purpose:   Return the Age in years.
    'Arguments: varDOB = Date Of Birth
    '           varAsOf = the date to calculate the age at, or today if missing.
    'Return:    Whole number of years.
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date  'Birthday in the year of calculation.

    Age = Null          'Initialize to Null

    'Validate parameters
    If IsDate(varDOB) Then
        dtDOB = varDOB

        If Not IsDate(varAsOf) Then  'Date to calculate age from.
            dtAsOf = Date
            dtAsOf = varAsOf
        End If

        If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
            dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
            Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function

To use this function in a standard module we create a module by right-clicking on the name of the application (TeachingDB in our case) and selecting insert>module.

Screenshot 2014-03-05 16.06.57

To rename it, we click on the newly created module and in the Properties window change the name. We are going to call our module modFunctions.

Screenshot 2014-03-05 16.07.19

Now we drop the code into modFunctions and save it.

Screenshot 2014-03-06 10.46.43So we have our module with a function. So, what about testing it?

Create a new module called modTest and drop this code in (it isn’t necessary to create a new module but for the sake of this post, we will):

Public Sub testAge()
Debug.Print Age(#5/25/1985#)
End Sub

Screenshot 2014-03-06 10.47.24This code will print the age of someone who was born on 25th May 1985.

Let us call it from the immediate window.

call testAge

Screenshot 2014-03-06 10.47.49It prints out the number 28. (This may vary depending on when you read this post).

Pretty standard stuff, right? But how can we implement the same idea with class modules? Let’s take a look.

Right-click on the name of the application (TeachingDB in our case) and select insert>Class Module.

Screenshot 2014-03-06 13.01.51To rename it, we click on the newly created class module name and in the Properties window change the name. We are going to call our module clsFunctions.

Screenshot 2014-03-06 13.02.07

Now we drop the code into clsFunctions and save it.

Screenshot 2014-03-06 13.06.06So far, so similar! But now we start to see a difference in implementation.

We are going to add a new sub-procedures to modTests to test the class module.

Enter this code:

Public Sub ClassTest()
Dim cFunctions As New clsFunctions
Debug.Print cFunctions.Age(#5/25/1985#)
End Sub

Screenshot 2014-03-06 13.06.19And to test it, we will call the sub-procedure from the immediate window.

Screenshot 2014-03-06 13.16.44It produces exactly the same result as the function in the standard module. But what was going on with…

Dim cFunctions As New clsFunctions
Debug.Print cFunctions.Age(#5/25/1985#)


Well class modules are kind of like blueprints. You use them as the schema to create objects in Access.

Wow, 3D printers to the ready!

Not that kind of object. Objects in computing refer to abstract things and is a vague reference to say the least. In our example we have created a clsFunctions object which contains one method (Age). It doesn’t feel like we have created anything but we really have.

When we write Dim cFunctions As New clsFunctions it is similar in syntax to Dim strName as String, right? Well a string is something, isn’t it? A data type to be exact. So, using classes, we are venturing into the world of creating objects that we can work with and this represents an important shift in the way we program.

Using a language based analogy, we are going from using verbs (standard modules – focus on actions) to nouns (class modules – focus on objects). Let us take a look at another example of how we use class modules.

Another Example

Let us go through another example along similar lines. We want to create a class that handles various forms of conversion (Kilometers to Miles, Centimeters to Inches, etc.).

Create a class module called clsConversion and add this code:

Public Function KilometersToMiles(value As Double) As Double
KilometersToMiles = Round(0.621371192 * value, 2)
End Function

Public Function CentimetersToInches(value As Double) As Double
CentimetersToInches = Round(2.6 * value, 2)
End Function

Public Function KiloToPounds(value As Double) As Double
KiloToPounds = Round(2.20462 * value, 2)
End Function

Public Function PoundsToStones(value As Double) As Double
PoundsToStones = Round(0.0714286 * value, 2)
End Function

This code is a set of methods (when in a class module we refer to functions as methods) that take an input value As Double and multiply it by the relevant amount (0.621371192 for kilometers to miles). The new amount is then rounded to 2 decimal places. Now, let us see how we implement this.

In modTest add this code:

Public Sub Conversion()
Dim cConversion As New clsConversion
Debug.Print "5 Kilometers = " & cConversion.KilometersToMiles(5) & " miles"
Debug.Print "12 Centimeters = " & cConversion.CentimetersToInches(12) & " inches"
Debug.Print "53 Kilos = " & cConversion.KiloToPounds(53) & " pounds"
Debug.Print "36 Pounds = " & cConversion.PoundsToStones(36) & " Stones"
End Sub

Dim cConversion As New clsConversion

Declare and instantiate cConversion as a new clsConversion object.

Debug.Print “5 Kilometers = ” & cConversion.KilometersToMiles(5) & ” miles”

This line of code simply prints out the concatenated statement 5 Kilometers = 3.11 miles in the immediate window. The other lines of code work exactly the same way.

If you have added the code for the class and modTest correctly, when you call Conversion from the immediate window, you will get this output in the immediate window:

5 Kilometers = 3.11 miles
12 Centimeters = 31.2 inches
53 Kilos = 116.84 pounds
36 Pounds = 2.57 Stones

Class modules represent an important step in your development as an Access developer and will enable you to sound impressive in the right company. In the coming posts, we will be examining other ways you can use class modules to enhance your Access applications.

Related Posts

02 – Multi-Data Type Arrays
03 – Global Class Modules
04 – Unbound Forms i – How To Create An Unbound Form
05 – Unbound Forms ii – Class Module Properties
06 – Unbound Forms iii – CRUD
07 – Unbound Forms iv – Form Code

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.

In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!


  • David says:


    I am getting a Compile Error – ‘User-defined type not defined’
    when calling Conversion. I can’t see where the problem is in the code

    • Robert Austin says:

      Hi David. This means that Access can’t recognize the class module you have created. Ensure that you have spelled the name of it correctly (use intellisense to help with that) and it should work.

      If you are still having problems, email your code and I will take a look.

Leave a Reply

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