Introduction.
If you have not seen the earlier Posts on the Microsoft Access Class Module, please go through them before continuing. The links are given below.
ClsArea A Class can serve as a base class for other class objects, allowing its calculations to be reused as part of more advanced operations. For instance, it could be integrated into a class designed to calculate the volume of an object, where area is only one step in the overall computation.
The dbl prefix in the dblLength and dblWidth Property procedure names simply indicate that these properties expect double-precision numeric values. Similarly, if we were to rename the property procedures to Quantity and UnitPriceMultiplying one by the other would yield the Total Price of an item.
This demonstrates the flexibility of the ClsArea class. Wherever the result of multiplying two values is required—such as TotalPrice * TaxRate to compute tax, or TotalPrice * DiscountRate to determine a discount—it can be adapted as a base class to fit the scenario.
Although we started with a simple class module, it has the potential to be used as part of many other derived classes. The possibilities are limited only by your imagination and creativity.
Currently, our ClsArea Class calculates the area of materials, rooms, or similar objects using only the Length and Width properties. It does not yet support shapes like triangles or circles. However, it can be extended into a new class object that calculates the volume of rooms or warehouses to determine storage capacity. To achieve this, we would simply introduce an additional property, such as Height, into the design.
The Volume Class: ClsVolume.
Let’s now create a new class module named ClsVolume, using ClsArea as its base class.
-
Insert a new Class Module.
-
In the Properties Window, change the Name property to
ClsVolume. -
Type or copy and paste the following code into the class module:
Option Compare Database
Option Explicit
Private p_Area As ClsArea
Private p_Height As Double
Private Sub Class_Initialize()
Set p_Area = New ClsArea
End Sub
Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub
Public Property Get dblHeight() As Double
dblHeight = p_Height
End Property
Public Property Let dblHeight(ByVal dblNewValue As Double)
Do While Val(Nz(dblNewValue, 0)) <= 0
dblNewValue = InputBox("Negative/0 Values Invalid:", "dblHeight()", 0)
Loop
p_Height = dblNewValue
End Property
Public Function Volume() As Double
If (p_Area.Area() > 0) And (p_Height > 0) Then
Volume = p_Area.Area * p_Height
Else
MsgBox "Enter Valid Values for Length,Width and Height.", , "ClsVolume"
End If
End Function
Let’s examine the code line by line. On the third line, we declare a private member p_Area of type ClsArea — a reference to an ClsArea instance that this class will use internally. The next line declares a private field p_Height As Double to store the height value. Both members use the p_ prefix to indicate private scope; they will be accessed and validated through property procedures rather than directly from outside the class.
The Class_Initialize() and Class_Terminate() Sub-Routines.
The next two subroutines—Class_Initialize() and Class_Terminate()—play a crucial role in the ClsVolume class.
-
Class_Initialize()runs automatically when anClsVolumeobject is created in a standard module. Within this routine, we instantiate theClsAreaobject in memory, ensuring that all of its functionality is available to the new class. -
Class_Terminate()is triggered when we explicitly clear theClsVolumeobject with a statement such asSet ClsVolume = Nothing. At this point, the subroutine ensures that the memory allocated to theClsAreaobject is also released properly.
Property Get dblHeight The procedure simply returns the current value of the private field p_Height to the calling program.
Property Let dblHeight The procedure validates the value passed into the NewValue parameter before assigning it to the private property p_Height. This safeguard prevents invalid values (such as zero or negative numbers) from being stored.
The Public Function Volume() calculates the volume by calling the p_Area.Area() function. The returned area value is then multiplied by p_Height using the expression:
Before executing this calculation, a validation check ensures that p_Area.Area() returns a value greater than zero (which confirms that both p_Area.dblLength and p_Area.dblWidth contain valid values) and that the p_Height Property is also greater than zero. Only when all three properties hold valid values is the volume calculation performed.
Note: Since the p_Area object of the ClsArea Class is defined as a private member of the ClsVolume class, we must expose its properties (strDesc, dblLength, dblWidth) and its Area() function to the outside world. This is done by creating corresponding Get/Let property procedures in the ClsVolume class module, effectively making these members accessible for use while maintaining encapsulation. The Let/Get Property Procedures.
Here’s the refined code you can add to your ClsVolume class module. These procedures expose the strDesc, dblLength, dblWidth, and Area() members of the private p_Area object to the outside world:
Public Property Get strDesc() As String
strDesc = p_Area.strDesc
End Property
Public Property Let strDesc(ByVal NewValue As String)
p_Area.strDesc = NewValue
End Property
Public Property Get dblLength() As Double
dblLength = p_Area.dblLength
End Property
Public Property Let dblLength(ByVal NewValue As Double)
p_Area.dblLength = NewValue
End Property
Public Property Get dblWidth() As Double
dblWidth = p_Area.dblWidth
End Property
Public Property Let dblWidth(ByVal NewValue As Double)
p_Area.dblWidth = NewValue
End Property
Public Function Area() As Double
Area = p_Area.Area()
End Function
Check the strDesc property procedures (Get/Let) carefully. The choice of the procedure name strDesc is arbitrary—you could use a different name if you prefer. However, since the original property in the ClsArea class is also named strDesc Reusing the same name here helps maintain a clear connection with the base class and makes the relationship between the two classes more intuitive.
In the Get dblLength() property procedure, the expression to the right of the equals sign p_Area.dblLength retrieves the stored length value from the ClsArea object and returns it to the calling program.
The corresponding Let procedure assigns the incoming parameter value (NewValue) to the p_Area.dblLength property of the ClsArea object. Notice that we do not run a separate validation check here—the validation is already enforced within the ClsArea class itself when the value is assigned.
The same logic applies to the dblWidth property. Its Get and Let procedures expose the corresponding property of the p_Area object while delegating validation to the base class.
Finally, the p_Area.Area() function is surfaced through the ClsVolume class, making it directly accessible to the calling program. This ensures that the Area method defined in the base class can be reused seamlessly in the derived class.
The ClsVolume Derived Class Module Code.
The completed code of the ClsVolume Class Module is given below.
Option Compare Database
Option Explicit
Private p_Area As ClsArea
Private p_Height As Double
Private Sub Class_Initialize()
‘Open ClsArea Object in Memory with the name p_Area
Set p_Area = New ClsArea
End Sub
Private Sub Class_Terminate()
‘Removes the Object p_Area from Memory
Set p_Area = Nothing ‘
End Sub
Public Property Get dblHeight() As Double
dblHeight = p_Height
End Property
Public Property Let dblHeight(ByVal dblNewValue As Double)
Do While Val(Nz(dblNewValue, 0)) <= 0
dblNewValue = InputBox("Negative/0 Values Invalid:", "dblHeight()", 0)
Loop
p_Height = dblNewValue
End Property
Public Function Volume() As Double
If (p_Area.Area() > 0) And (Me.dblHeight > 0) Then
Volume = p_Area.Area * Me.dblHeight
Else
MsgBox "Enter Valid Values for Length,Width and Height.",vbExclamation , "ClsVolume"
End If
End Function
‘ClsArea Class Property Procedures and Method are exposed here
Public Property Get strDesc() As String
strDesc = p_Area.strDesc
End Property
Public Property Let strDesc(ByVal NewValue As String)
p_Area.strDesc = NewValue
End Property
Public Property Get dblLength() As Double
dblLength = p_Area.dblLength
End Property
Public Property Let dblLength(ByVal NewValue As Double)
p_Area.dblLength = NewValue
End Property
Public Property Get dblWidth() As Double
dblWidth = p_Area.dblWidth
End Property
Public Property Let dblWidth(ByVal NewValue As Double)
p_Area.dblWidth = NewValue
End Property
Public Function Area() As Double
Area = p_Area.Area()
End Function
At this point, you might be thinking: “This feels like double work—wouldn’t it be better if we could somehow skip the repeated property procedures in the ClsVolume class?” Or perhaps, “Why not just add the dblHeight property directly into ClsArea and calculate both Area() and Volume() from there?”
That’s a fair question. But the purpose here is to demonstrate how a base class object can be incorporated into the design of another class object. This pattern shows the power of reusability and encapsulation in VBA class design.
The key benefit of building reusable class modules is that your main programs remain simple, while all the complexity stays hidden inside the class object. This separation makes your code easier to maintain, reuse, and extend.
Of course, there are more compact approaches that could reduce code repetition—we’ll explore those later. For now, let’s continue with our original plan so you can fully understand the step-by-step process.
The Main Program that Uses the ClsVolume Class.
Let us test our new ClsVolume Class in the main Program in the Standard Module. The sample code is given below.
Public Sub TestVolume()
Dim vol As ClsVolume
Set vol = New ClsVolume
vol.strDesc = "Warehouse"
vol.dblLength = 25
vol.dblWidth = 30
vol.dblHeight = 10
Debug.Print "Description", "Length", "Width", "Height", "Area", "Volume"
With vol
Debug.Print .strDesc, .dblLength, .dblWidth, .dblHeight, .Area(), .Volume()
End With
End Sub
You can see how simple the main program is, without the printing lines.
Copy and paste the code into a Standard Module. If the Immediate (Debug) Window is not already open, press Ctrl+G to display it. Next, click anywhere inside the code and press F5 to run the procedure.
The sample output displayed in the Debug Window should look similar to the example shown below: Description Length Width Height Area Volume
Warehouse 25 30 10 750 7500
Description Length Width Height Area Volume
Warehouse 25 30 10 750 7500
Validation Checks Performance Tests.
Next, we will run a few tests to confirm that the validation checks built into the base class (ClsArea) still works when values are passed through the ClsVolume class. Recall that we also added additional validation in the Area() and Volume() functions.
Let’s test them one at a time.
Test 1: Pass a negative value to the ClsArea.dblLength property through the ClsVolume class. This should immediately trigger the error message and invoke the InputBox() function within the Do While…Loop, prompting you to enter a valid (positive) value.
1. Replace the Value 25 with -5 in the line Vol.dblLength = 25 and press the F5 Key to run the Code.
The validation check will trigger the error and will ask for a value greater than zero. Enter a value greater than 0. After that, restore the value 25.
2. Disable the line Vol.dblHeight = 10 by inserting a comment symbol (‘) at the beginning of the line as shown: ‘Vol.dblHeight = 10. After the change, press the F5 Key to run the Code.
If no input values are provided for the properties, the Volume() Function will generate an error, indicating that all three properties—dblLength, dblWidth, and dblHeight—must contain valid values before the calculation can proceed.
In the same way, you can also test the behavior of the Area() function to verify that it responds correctly to missing or invalid inputs.
To make testing easier, we can create a data-printing function that accepts an ClsVolume object as a parameter and prints its property values and calculated results in the Debug Window.
The Changed Code of Main Programs.
The changed Code for both Programs is given below:
Public Sub TestVolume() Dim Vol As ClsVolume Set Vol = New ClsVolume Vol.strDesc = "Warehouse" Vol.dblLength = 25 Vol.dblWidth = 30 Vol.dblHeight = 10 Call CVolPrint(Vol) Set Vol = Nothing End Sub
Public Sub CVolPrint(volm As ClsVolume) Debug.Print "Description", "Length", "Width", "Height", "Area", "Volume" With volm Debug.Print .strDesc, .dblLength, .dblWidth, .dblHeight, .Area, .Volume End With End Sub
Next week, we will build the Volume Class Object.
The Links of All Pages on this Topic.
Earlier Post Link References:
- 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
