MS-Access Class Module and VBA

Introduction.

In the last few weeks we have learned how to use User-Defined Type (UDT)  by creating a complex data structure and we know UDT’s strengths or weaknesses, more or less by now.  If you have not gone through those Articles, then you may visit them. Use the following links:

• 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 on Forms and Reports in Microsoft Access. Form/Report Class Module programs are mostly Event-driven (Button-Clicks, Before-Update, Form/Report Current Event, and so on) small routines.

More serious data processing Programs will be written in Standard Modules. Several programs, not necessarily related, can be written in one Standard Module, to do different tasks.

Class Modules are different. They are used for building Custom Objects and one Class Module is used for only one object.

The Basics of Stand-alone Class Module.

Let us create a simple Class Module from scratch and learn the basics.

1. Start Microsoft Access and Open a Database or Create a new one.  If it is a New Database then save it to the existing Trusted Location (Folder) or add the new Location to the trusted location list.  Click Office Button –>Access Options—> Trust Center –>Trust Center Settings.  Add the database folder to the list and click OK.

2. Open the VBA Editing Window (Alt+F11).

3. Click on Insert Menu and select the Class Module from the list. A new Class Module is inserted.
   
   

4. In the left panel, there is a Class Modules Folder Icon, beneath that you can the Class Module name Class1.
5. Click on that and select the Properties Window Option from View-Menu. Change the name Class1 to ClsArea. This is the name of our Custom Object.
   
   
   

Note: The Class Name: ClsArea becomes the Object Name. That means, wherever we use this object, it will be like a normal variable declaration: Dim xyz As ClsArea.  We did write a similar statement for User-Defined Data Type declarations.

Next, we will add three Properties (Variables) of the Object (for Description, Length, and Width) at the top of the module, under the Option Compare Database and  Option Explicit lines.  Type in the following lines in 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 identified as Properties of the VBA Custom Class Object, and they are declared in the Global Declaration area with Public scope. Save the Class Module.

Note: Our Class Module Object (Class Object) and lines of code will not be this simple.  It will undergo changes with several lines of code. Better be prepared to follow them step by step, without losing track of each stage of change. This is a simple area-calculation Class (area = length * width), that simple. It will gradually undergo changes so that you will know why those changes are necessary.

We will write a small program in a Standard Module to test our new Class Module. Insert a Standard Module from Insert Menu.  You may  Type or Copy and Paste the following Code into the Standard Module, overwriting the existing line in the Module:

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 declares an Object Variable as we do for a normal variable, like Dim Desc as String.  But, this is not an ordinary variable we are declaring an Object Variable oArea of our Class Object ClsArea.  Since it is an object a simple Dimension statement alone is not enough because it will not allocate any memory space, like other normal Variables which we use, to store values into our locally defined Object oArea Properties. The member Variables we have declared in our ClsArea Class Objects are known as Properties of the ClsArea Object.

The Set statement in the next line with the New keyword is required, to create an instance of an object out of ClsArea in memory, with the Object Instance Name oArea.  We can open several instances of the same Class Object, in memory, in this way if needed,  (We will learn about them in the coming weeks) so that we can store values in its Properties (p_Desc, p_Length, p_Width).  The p_ prefix to the variables is an indicator that the Scope of variables is Private, i.e., the Variables are not visible outside the Class Module if the variable is declared with the keyword Private, but now it is declared as Public. The variable name can be any valid name.

Note: We have not declared them as Private yet. We are on the way toward that change.

Immediately after the keyword Set the local Object name (you can pick a suitable name you prefer, but it should conform to the normal variable name rules) followed by an equal sign and the keyword New and the Class Module Name (ClsArea) to create an instance of the clsArea Object in memory with all its Properties (Variables). 

There is a shortcut for this two-line code. The actions of both these lines of code can be achieved with one 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 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 it doesn't appear then go to the Options dialog from the Tools menu box and put a checkmark in Auto List Members in the Edit Tab.

Before exiting the Function the last statement should be Set oArea = Nothing. This statement explicitly releases the memory occupied by the ClsArea Object for other uses.

Whatever we do with the instantiated Custom Object should be coded between the first and last Set statements.

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 and we are going to rectify them.

The first one is that we have declared all Variables (or Properties) with Public Scope.  Because of that they are visible to other VBA programs and may get their value changed directly.  The second issue is that it will accept any invalid values, like negative or zero values, which is not suitable for our Class Object.  We have to incorporate some validation checks before accepting the values in the variables.

The first problem we can solve easily by changing the variable declarations from Public to Private.  When we do that we should have some indirect method to store and retrieve values from the Properties declared with Private Scope. That is the purpose of the Get and Let Property Procedures, for each Property of the Object.  Let us make these changes in the Class Module. 

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

Creating Property Procedures

Select Procedure from 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. Both Procedure names are the same strDesc.  By default, the returned data type is a Variant in the Get Procedure and the Parameter data type is also inserted as a Variant type in the Let Procedure.  We can change to specific types as needed, which we did and changed to String type.  The first three letters str in strDesc give the User a hint that the Property expects a String data type value. Change the Let Property Procedure Parameter Variable Name vNewValue to strNewValue

When we insert the Property Procedure, they are always inserted with the Get and Let Property Procedure pair.

Now, take a look closely at the expression we have written within the Get Procedure.  On the left side of the = Sign, the name of the Get Procedure strDesc acts as a variable to return the value copied from the Private Variable p_Desc  to the calling program. 

The Let Procedure strDesc accepts String value in parameter Variable strNewValue.  The input value is transferred into our Private Property p_Desc.

The point to note here is that there is no direct access to our Private Property p_Desc to the outside world, except through the Get Property Procedure.  Transportation of values From/To the Variable (Property) p_Desc is always routed through the Get/Let Property Procedures only and subject to Validation checks(not yet implemented),  We will introduce validation checks on values input (Let Procedure) into the Property later.

The Get/Let Procedures are executed automatically depending on what we do with the Object property in an expression in VBA Programs. 

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 stopped using it at all.

Here, if you are only reading some value from a Variable and not storing anything in it directly, then you can omit the Let Procedure and use only the Get Procedure.

This rule applies to the Let procedure also.  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 then omit the Get Procedure.

The Get and Let 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, we will Get the existing value from the Private Variable p_Desc, and 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 Procedure is called, and the Let Procedure is run when the object property procedure name appears to the left of the equal (=) sign.

Insert two sets of Property Procedures for the variables p_Length and p_Width. When you give the Procedure names in the Name control give the names dblLength and dblWidth to give a hint 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 are through completing the above code, then let us make changes to our test program, to reflect the changes we 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 enter a dot (.) immediately after the object name oArea (oArea.), the list of Property Procedure names is displayed by the VBA IntelliSense, and you may select the required one from the list without typing it manually.

The purpose of this Class Object is to calculate the area of something, like the Area of a Room, Carpet, Floor Tile, or whatever material, which has values of Length and Width.  That means we need a Public Function to calculate the Area of whatever item’s Length, Width, and Descriptor Values are entered into the Class Object.

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 may insert this Function from the Insert Menu by entering Area in the Name Control, selecting Function from the Type option group, and Public as Scope into the ClsArea Class Module.  Complete the Function by entering the line in the middle.

We can directly address p_Length and p_Width variables (because the Function Area() is part of the Class Module) in the expression for calculating the Area.  But, we are taking the proper route and calling the Get Procedures dblLength and dblWidth for calculation.  You might have noticed the reference term Me. (Me dot) is used to qualify the dblLength, and dblWidth Get Procedures as we used to write in Form/Report Class Modules, to refer to the current Object in memory and its Properties.  As I stated earlier, our Custom Class Object can have several Object instances opened in memory at the same time and the Me keyword refers to the current instance that the Function Area() belongs to.

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() program is executed automatically when we instantiate an Object with the New Keyword.  This program can be used to set default values into variables or Instantiate other  Objects in memory.  One Class Object may use other Classes as child object(s) and needs to be instantiated.  This aspect we will explore further and learn how to do it later.

The Class_Terminate() program runs when we try to clear the object from memory when the Nothing keyword is run in the statement Set oArea = Nothing.  When the program that uses the Class Object ends, the Instance of the Object in memory is removed by default.  But it is a good programming practice that we use Set oArea = Nothing statement as the last executable statement in our programs to clear the object from memory.

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.

Here, we are not writing an ordinary Program but developing a Custom Object that can be used many times or can be part of other Objects, wherever we need it without worrying about how it works, from the user's point of view.  Microsoft Access has many built-in Objects/Functions that we use all the time without worrying about how it works, by setting their Properties or Parameters and getting the work done.

We have one more issue to take care of, the validation checks on values input into the dblNewValue  Parameter in the Let Property Procedures of dblLength() and dblWidth(), to ensure that valid values are assigned to Object Properties p_Length and p_Width. 

Negative or Zero Values entered are considered invalid and we have to take precautions to see that the correct values are received in the Object Properties before attempts are made for calculations. 

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 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 correctly working, wit 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.

We have tested the Class Object for only one item.  We need to calculate the area of several items (say the area with 5 bedrooms or 10 different-sized Carpets and so on.  We are told that once an object is developed we can instantiate it several times in memory, assigning a different set of values to each instance of the Object, and can work with all of them.

Besides that, this Object can be used as part of other Objects we develop with lesser code because part of our new Class Object is already developed in the ClsArea Class Module.

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