LEARN MS-ACCESS TIPS AND TRICKS: Expression

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 an ClsVolume object is created in a standard module. Within this routine, we instantiate the ClsArea object in memory, ensuring that all of its functionality is available to the new class.
Class_Terminate() is triggered when we explicitly clear the ClsVolume object with a statement such as Set ClsVolume = Nothing. At this point, the subroutine ensures that the memory allocated to the ClsArea object 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:


Volume = p_Area.Area * p_Height

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:

SELECT Orders.OrderID, Orders.CustomerID, Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderVal FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.CustomerID;

SELECT Orders.*, (SELECT Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue FROM [Order Details] AS ODetails WHERE ([ODetails].[OrderID] = [Orders].[OrderID]) GROUP BY [ODetails].OrderID) AS OrderVal, [OrderVal]*0.12 AS Tax FROM Orders;