MS-Access Class Module and VBA

Introduction.

Over the past few weeks, we explored how to work with User-Defined Types (UDTs) by creating complex data structures, gaining a fair understanding of their strengths and limitations. If you haven’t reviewed those articles yet, I recommend visiting them through the links below:

• User-Defined Data Type-2
• User-Defined Data Type-3

In Microsoft Access, there are two types of VBA Modules.

1. Standard Modules
2. Class Modules

We have worked with Class Modules in Forms and Reports in Microsoft Access. These modules typically contain small, event-driven routines—for example, handling button clicks, the BeforeUpdate event, or the Form/Report Current event.

For more complex data-processing tasks, the code is usually placed in Standard Modules. A single Standard Module can contain several unrelated procedures and functions to perform different operations.

Class Modules, however, are different. They are designed to define and manage custom objects, and each Class Module is dedicated to representing a single object.

---

The Basics of a Stand-alone Class Module.

1. Let’s create a simple Class Module from scratch to understand the basics.

   1. Start Microsoft Access and open an existing database, or create a new one.

      • If you create a new database, save it in an existing Trusted Location (folder), or add the folder to your list of trusted locations.

      • To add a trusted location:

        • Click the Office Button → Access Options → Trust Center → Trust Center Settings.

        • Add your database folder to the list and click OK.

   2. Open the VBA Editor by pressing Alt + F11.

   3. In the VBA window, click on the Insert menu and select Class Module.

      • A new Class Module will be added to your project, ready for use.

   

   Right-click to open Large Image in New Tab/Window.

2. In the left panel, locate the Class Modules folder icon. Beneath it, you will see a class module named Class1. 

3. Click on Class1, then open the Properties window from the View menu. Change the name from Class1 to ClsArea—this will be the name of our custom object.

Right-click to open Large Image in New Tab/Window.

Note: The class name ClsArea becomes the object type. This means that whenever we use this object, we can declare it like a regular variable:

Dim xyz As ClsArea

This is similar to how we declare variables for user-defined data types.

Next, we will add three properties (variables) to the object: Description, Length, and Width. These should be placed at the top of the class module, immediately below the Option Compare Database  Option Explicit Lines. Enter the following lines into the class module:

Option Compare Database
Option Explicit

Public p_Desc as String
Public p_Length as Double
Public p_Width as Double

These variables are recognized as properties of the VBA custom class object and are declared in the global declaration area with Public scope. Save the class module.

Note: Our class module and its code will not remain this simple. Over time, it will evolve with more lines of code. It is important to follow each step carefully to understand the purpose behind each change. At this stage, we are creating a simple class for area calculation (Area = Length * Width). Gradually, we will enhance it, and you will learn why each modification is necessary.

Next, we will write a small program in a standard module to test our new class module. Insert a standard module via the Insert menu, then type or copy and paste the following code into the module, replacing any existing content:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.

Set oArea = Nothing

End Function 

The Dim statement is used to declare an object variable, similar to how we declare a regular variable—for example:

Dim Desc As String

However, in this case, we are not declaring an ordinary variable, like String or Integer. We are declaring an object variable oArea of our class ClsArea. Because it is an object, a simple Dim statement alone is not sufficient; it does not allocate memory for the object or its properties.

The member variables we previously declared in the ClsArea class are known as properties of the ClsArea object. To use these properties, the object itself must be loaded (instantiated) into memory. 

The Set statement, combined with the New keyword, is required to create an instance of the ClsArea class in memory, referenced by the object variable oArea. By doing this, we allocate memory and make the object ready for use.

Multiple instances of the same class can be created in this way, each independent of the others. (We will explore this in detail later.) Once instantiated, we can store values in its properties—p_Desc, p_Length, and p_Width.

 p_ The prefix is used here to indicate that these variables are intended as private members of the class (i.e., not visible outside the class module if declared with the Private keyword). However, in our current example, they are declared as Public, which makes them accessible outside the class. The actual variable names can be any valid identifiers; the prefix is simply a naming convention for clarity.

Note: At this stage, we have not yet declared the variables as Private. We will make that change later as the class evolves.

To create an object instance, use the Set statement. Immediately after the keyword 'Set', specify the local object name (you may choose any valid variable name, following standard naming rules), then an equal sign, the keyword 'New', and finally the class module name (ClsArea). This creates an instance of the ClsArea object in memory, complete with all its properties (variables).

There is also a shortcut. The two lines of code required for declaration and instantiation can be combined into a single statement, as shown below:

Dim oArea As ClsArea
Set oArea = New ClsArea

'the shortcut to the above two statements
Dim oArea As New ClsArea

When you type the next line of oArea followed by a dot (.) separator, the following display will appear to show the list of available Custom Object Properties to select from and assign appropriate values to them.

Right-click to open Large Image in New Tab/Window.

If the list of properties and methods does not appear as shown above, open the Options dialog from the Tools menu and, on the Edit tab, enable the Auto List Members option.

Before exiting the function, always include the statement:

Set oArea = Nothing

This explicitly releases the memory allocated to the ClsArea object, making it available for other uses.

All operations involving the instantiated custom object should be written between the first Set statement (where the object is created) and the final Set ... = Nothing statement (where the object is released).

---

The ClsArea Class Object Test Program.

The completed Class Testing Program Code is given below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.p_Desc = "Carpet"
oArea.p_Length = 25
oArea.p_Width = 15

Debug.Print "Description", "Length", "Width"

Debug.Print oArea.p_Desc, oArea.p_Length, oArea.p_Width

Set oArea = Nothing

End Function

Click somewhere in the middle of the Code and press F5 to run the program. The Run of the program is given below for reference.

Description   Length        Width
Carpet         25            15 

Public|Private Scope of Object Properties.

Our simple class module object has a few drawbacks that we now need to address.

The first issue is that all variables (properties) have been declared with the Public scope. This makes them directly accessible from other VBA programs, meaning their values can be changed freely—something we do not want.

The second issue is the lack of validation. Currently, the class accepts any values, including invalid ones such as negative or zero. We must introduce validation checks on values entered before they are assigned to these variables.

The first problem can be solved easily by changing the variable declarations from Public to Private. However, once declared Private We cannot access them directly. Instead, we need an indirect mechanism to store and retrieve values. This is precisely what Property procedures—Get and Let—are designed for. We will create these procedures for each property of the object.

Let’s now make these changes in the class module.

Open the Class Module ClsArea. Change the scope from Public to Private for all three variables.

Creating Property Procedures

Select Procedure from the Insert Menu, type strDesc in the Name text control, select Property in the Type Option Group, and Public in the Scope option group.  Click OK to insert the Property Procedures for the Private p_Desc Variable (Property).

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
   strDesc = p_Desc 'return the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
   p_Desc = strNewValue ‘store the value into p_Desc
End Property

Both the Get and Let procedures are declared as Public Property procedures, and both use the same name: strDesc. By default, the Get procedure returns a Variant type, and the parameter in the Let procedure is also created as a Variant. However, we can (and should) change these to more specific data types as needed. In our case, we changed them to the String type.

The naming convention also helps: the str prefix in strDesc indicates that this property expects a String value. Likewise, we should rename the parameter variable in the Let procedure from vNewValue to strNewValue for clarity.

When a property is added, Access inserts both the Get and Let procedures as a pair.

Now, let’s take a closer look at the code. In the Get procedure, the procedure name (strDesc) acts like a variable on the left side of the equals sign (=). It returns the value of the private variable p_Desc to the calling program.

In the Let procedure, strDesc accepts a String value through its parameter (strNewValue). The value passed in is then stored in the private property p_Desc.

The key point here is that there is no direct access to our private property 'p_Desc' from outside the class. All data transfer to and from p_Desc must go through the Get/Let procedures. The Let Property Procedure can perform validation checks before assigning values to private Properties (We will add these checks shortly).

Finally, remember that the Get or Let procedure is executed automatically, depending on whether you are reading from or writing to the property in your VBA code.

The Get procedure is executed when we use the Property Name in an expression in the following manner:

‘ Reads the value from p_Desc to Print

Debug.Print oArea.strDesc 'Property Get strDesc() is called

OR

‘ Reads the value from p_Desc and assigns it to the variable X

X = oArea.strDesc 'Property Get strDesc() is called for the value

The Let Property Procedure is run when we try to assign a value to the Property Name. Check the example expression in our Test Program below:

oArea.strDesc = “Carpet” 'Property Let strDesc() is executed

In earlier BASIC Language books, you can see the usage of the keyword LET.

LET X = 25 ‘ LET is optional

Since it was optional, the statement works without it, and I stopped using it.

If you are only reading value from a variable and not storing anything directly, then you can omit the Let Property Procedure and write only the Get Property Procedure.

This rule applies to the Let Property procedure.  You may use only the Let Procedure if you are assigning some value to a Private Variable, but not reading anything back from the same variable from outside, then omit the Get Procedure.

The Get and Let Property Procedures will run one after the other if our expression is something like the following:

oArea.strDesc = oArea.strDesc & “ – King Size.”

In the above expression, get the existing value from the Private Variable p_Desc, modify the description, and store it back into the same variable.  In short, in an expression, if you use the Property name to the right of the equal sign (=) the Get Property Procedure is called, and the Let Property Procedure is run when the object property procedure name appears to the left of the equal (=) sign.

The Let and Get Property Procedures should use the same Procedure Name:

Public Property Get strDesc() As String
   strDesc = p_Desc 'return the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
   p_Desc = strNewValue ‘store the value into p_Desc
End Property

Next, insert two additional sets of Property procedures for the variables p_Length and p_Width.

When assigning the procedure names in the Name control, use dblLength and dblWidth. The dbl prefix indicates to the user that these properties expect Double-precision numbers as input.

ClsArea Class Object with its Property Procedures.

The completed code so far, with dblLength and dblWidth Property Procedures, is given below for reference and to update your code.

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
  dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
  p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
  dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
  p_Width = dblNewValue
End Property

The Test Program with the Changes.

If you have completed updating the above code, then let us make changes to our test program to reflect the changes made here.  The modified sample code is given below.

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

‘Property Let procedures called here

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width"

‘Property Get Procedures called here to print
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth

Set oArea = Nothing

End Function

When you type a dot (.) immediately after the object name—for example, oArea.—VBA IntelliSense displays a list of the available property procedure names. From this list, you can simply select the required property instead of typing it manually.

The purpose of our class object is to calculate the area of something—such as a room, carpet, floor tile, or any other item that has measurable length and width. To achieve this, we need to add a Public Function to the class. This function will calculate the area based on the Length and Width values entered into the class object, while also making use of the descriptor value for context.

ClsArea Object Method: Area()

Here is the Code for the Public Function:

Public Function Area() As Double
   Area = Me.dblLength * Me.dblWidth
End Function

You can insert this function into the ClsArea class module using the Insert menu. In the Name control, enter Area; select Function in the Type option group; and choose Public as the scope. Then, complete the function by adding the calculation line inside it.

Since the Area() function resides within the class module, we could directly reference the private variables p_Length and p_Width in the calculation. However, we will take the proper approach and use the corresponding Get procedures (dblLength and dblWidth).

Notice the use of the keyword Me (as in Me.dblLength and Me.dblWidth). Just like in Form or Report class modules, 'Me' refers to the current object instance in memory. Because multiple instances of our custom class object can exist simultaneously, 'Me' ensures that the function works with the correct instance of the object.

The Test Function with Modification.

Modify our Test Function ClassTest1() to incorporate the Area() function output as below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width", "Area"
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth, oArea.Area

Set oArea = Nothing

End Function

The change is only in the Debug.Print statements.  Run the code and check the Debug Window for the result.

There are two Event Procedures required in the Custom Class Modules: Class_Initialize() and Class_Terminate(). 

Auto-Executing Methods.

The Class_Initialize() procedure is executed automatically whenever an object is instantiated with the New keyword. This procedure is often used to assign default values to variables or to instantiate other objects in memory. A class object can also make use of other classes as child objects, which may need to be instantiated during initialization. We will explore this aspect in more detail later.

The Class_Terminate() procedure is similar in concept to the Form_Unload() event procedure. It runs automatically before the class object is closed. Normally, the statement Set oArea = Nothing is sufficient to explicitly release a class object from memory.

As a good programming practice, always include it Set oArea = Nothing as the final executable statement in your procedures. This ensures that the object is properly cleared from memory and that system resources are released.

We will add the above programs to our Class Module.  Add the following code at the end of your Class Module:

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0

   'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

If you would like to test these two subroutines, then remove the Comment symbol and make the MsgBox active.  Run your test program one more time.  You will find the Initialize message appears in the beginning (Click OK to continue)  and the Terminate message appears at the end of the Test Program.

I know what you have in your mind by now, like “So much code for multiplying two variables together”.  It is true from that perspective, but we will be writing code for similar problem-solving issues repeatedly every time, duplicating code for validation checks and for other logical error safeguards.

At this stage, we are not simply writing an ordinary program—we are developing a custom object. Once created, this object can be reused multiple times or even embedded within other objects, wherever needed, without the user having to worry about its internal workings.

This is the same principle behind Microsoft Access’s many built-in objects and functions. We use them every day—by setting their properties or passing parameters—to get work done, without needing to know how they are implemented behind the scenes. Our custom class object follows the same approach.

There is one more issue we need to address: adding validation checks to the values passed into the dblNewValue parameter of the Let property procedures for dblLength() and dblWidth(). These checks are necessary to ensure that only valid values are assigned to the object properties p_Length and p_Width.

Specifically, negative or zero values should be treated as invalid. To prevent this, we must add safeguards that ensure only valid, positive values are assigned to the object properties before any calculations are performed.

Performing Validation Checks.

The modified Let Property Procedure Code segments are given below.  Make changes in your code accordingly.

Public Property Let dblLength(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
   Loop

   p_Length = dblNewValue
End Property


Public Property Let dblWidth(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
   Loop

   p_Width = dblNewValue
End Property

The Do While. . . Loop runs repeatedly till a valid value (greater than 0) is entered into dblNewValue by the User

Validation Checks in the Public Method: Area()

We need one more validation check in the Area() Function.  If the user calls the Area() function without entering valid values for Length and Width first, then the User must be informed about it.  We will check whether the p_Length and p_Width variables have valid values before running the expression for area calculation.  Here is the Code:

Public Function Area() As Double

   If (p_Length > 0) And (p_Width > 0) Then
       Area = p_Length * p_Width
   Else
       Area = 0
       MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
   End If

End Function

The Complete Code of ClsArea Object.

The fully completed Code of our Class Module ClsArea is given below:

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
    strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
    p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
   dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
    Loop

    p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
   dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
    Loop

    p_Width = dblNewValue
End Property

Public Function Area() As Double

   If (p_Length > 0) And (p_Width > 0) Then
       Area = p_Length * p_Width
   Else
       Area = 0
       MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
   End If

End Function

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0
End Sub

Private Sub Class_Terminate()
   'Exit
End Sub

Testing Property Procedures and Methods.

You may test our Custom Class Object by entering negative or 0 values as input to the dblLength and dblWidth Properties. 

In the Test Program, commented out the lines (oArea.dblLength=25 and oArea.dblWidth=15) to test the Area() function. It should display the Error message we have written within the function.

Our Area Calculation Class Object is now considered complete, and we have tested and found it working correctly, with the following VBA Function:

Public Function ClassTest2()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.strDesc = "Carpet"
oArea.dblLength = 0
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width", "Area"
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth, oArea.Area

Set oArea = Nothing

End Function

You may test it further for any logical errors that I have overlooked. If you come across anything that I didn't anticipate, please share it with me.

Future Plan for Testing.

So far, we have tested the class object with only a single item. In practice, however, we often need to calculate the area for multiple items—for example, five bedrooms or ten carpets of different sizes. Once a class object is developed, it can be instantiated multiple times in memory, with each instance holding its own unique set of values. This makes it possible to instantiate the same object multiple times, while keeping the values in each instance completely independent.

In addition, this object can serve as a building block for other objects we develop. By reusing the functionality already implemented in the ClsArea class module, we can minimize the amount of new code required and make our solutions both simpler to maintain and more efficient to build.

Next week, we will learn how to create an Array of Custom Objects to calculate the area of several items.

1. MS-Access Class Module and VBA
2. MS-Access VBA Class Object Arrays
3. MS-Access Base Class and Derived Objects
4. VBA Base Class and Derived Objects-2
5. Base Class and Derived Object Variants
6. Ms-Access Recordset and Class Module
7. Access Class Module and Wrapper Classes
8. Wrapper Class Functionality Transformation
9. Ms-Access and Collection Object Basics
10. Ms-Access Class Module and Collection Object
11. Table Records in Collection Object and Form
12. Dictionary Object Basics
13. Dictionary Object Basics-2
14. Sorting Dictionary Object Keys and Items
15. Display Records from Dictionary to Form
16. Add Class Objects as Dictionary Items
17. Update Class Object Dictionary Item on Form