Introduction.
- 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, 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:
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 representing 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 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:
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.
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 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.
- 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
"If it doesn't appear then go to Tools Menu and put check mark in Auto List Members in the VBA Editor Tab."
ReplyDeleteIf it doesn't appear then go to the Options dialog from the Tools menu box and put a check mark in Auto List Members in the Editor Tab.
Advised text correction is done.
ReplyDeleteThanks,
"the Parameter data type is also inserted as Variant in the Let Procedure."
ReplyDeleteI don't see anything being declared as Variant in the Let strDesc Procedure(see below).
Furthermore, does tmpA.strDesc in
tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
call both properties below?
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
1. Regarding the reference to the Variant Data Type, read the full paragraph for clarification.
ReplyDelete2. The Get/Let Property Procedure execution instances are explained with example expressions. Please go through the Article one more time.
Thanks,
Very well written, logical and very easy to follow. Thank you very much.
ReplyDelete