MS Access Class Module and VBA.
- User-Defined Data Type-2
- User-Defined Data Type-3
Over the past few weeks, we explored how to work with User-Defined Types (UDTs) by creating complex data structures and gained a fair understanding of their strengths and limitations. If you haven’t reviewed those articles yet, I recommend visiting them through the links below:
In Microsoft Access, there are two types of VBA Modules.
- Standard Modules
- 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 a single object.
The Basics of a Stand-alone Class Module.
Let’s create a simple Class Module from scratch to understand the basics.
-
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.
-
-
-
Open the VBA Editor by pressing Alt + F11.
-
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.
-
-
In the left panel, locate the Class Modules folder icon. Beneath it, you will see a class module named Class1.
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.
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:
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 need more lines of code. It is important to understand each step and 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:
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. With these, 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, and 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, and 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:
Private oArea As ClsArea Set oArea = New ClsArea 'the shortcut to the above two statements Private oArea As New ClsArea
When you type the next line of oArea, followed by a dot (.) separator, the following display will appear, showing the list of available Custom Object Properties to select from and assign appropriate values to them.
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:
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 anywhere in the middle of the Code and press F5 to run the program. The program output is shown 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 need to address now.
The first issue is that all variables (properties) are declared with 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. The entered values should undergo validation checks 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 the Private scope is declared, we don't have direct access to assign values. Instead, we need to define some temporary properties through which we can assign and retrieve the actual values from the Main Variables declared with Private Scope. 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 Public scope 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 ‘assign 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 you select Properties from the Insert menu, Access inserts both the Get and Let Property 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 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 a value from a variable and not storing a value, you can omit the Let Property Procedure and use only the Get Property Procedure.
This rule applies to the Let Property Procedure. You may use only the Let Procedure when assigning a value to a Private variable. If not reading the value from outside, then omit the Get Property 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, to 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, the 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 code, let us make some changes to our Test program to view 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 and 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 using 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 auto-executing subroutines (if present) in the Class Object Modules.
Class_Initialize()
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 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 cleared from memory and the system resources are released.
We will add the 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 at 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 problem-solving code repeatedly, 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 embedded in 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 parameters of the Let property procedures for dblLength() and dblWidth(). These checks are 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 to 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, the User must be informed. We will check whether the p_Length and p_Width variables have valid values before running the 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 the 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, comment out the lines (oArea.dblLength=25 and oArea.dblWidth=15) to test the Area() function. It should display the Error message we have written in 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 need only minimal code to 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.
- MS-Access Class Module and VBA
- MS-Access VBA Class Object Arrays
- MS-Access Base Class and Derived Objects
- VBA Base Class and Derived Objects-2
- Base Class and Derived Object Variants
- MS-Access Recordset and Class Module
- Access Class Module and Wrapper Classes
- Wrapper Class Functionality Transformation
- MS Access and Collection Object Basics
- MS-Access Class Module and Collection Object
- Table Records in Collection Object and Form
- Dictionary Object Basics
- Dictionary Object Basics-2
- Sorting Dictionary Object Keys and Items
- Display Records from Dictionary to Form
- Add Class Objects as Dictionary Items
- Update Class Object Dictionary Item on Form
