Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in memory.  The passed object become an extension or child Object of the Main Object in memory.  In our earlier program passing the child Object to the Target Object was done at the instantiating phase of our test program.  We have assigned  values to the passed Object Properties in later part of the program.  The next example is slightly different. 

Those who would like to go through the earlier Articles on MS-Access Class Module the links are given below:

• MS-Access Class Module and VBA
• MS-Access VBA Class Object Arrays
• MS-Access Base Class and Derived Objects
• VBA Base Class and Derived Object-2

This time we will open both Objects (ClsArea – the base class, ClsVolume2 – the target Class) separately in our test program.  Assign values into the Base Class ClsArea Properties, before passing it to the target Class ClsVolume2 Object.  Remember the Volume2 Class have only one Property, the p_Height Property, and it’s function Volume() needs the Length and Width Values of the Base Class ClsArea to calculate Volume.

1. Copy and Paste the following sample Test Code into a Standard Module.

   Public Sub SetNewVol2_2()
   'Method 2/2
   Dim CA As ClsArea
   Dim Vol As ClsVolume2
   
   Set CA = New ClsArea
   Set Vol = New ClsVolume2
   
   CA.strDesc = "Bed Room"
   CA.dblLength = 90
   CA.dblWidth = 10
   Stop
   'Here ClsArea class Object CA is passed to the 
   ‘Property procedure Set CArea of ClsVolume2 object Vol
   Set Vol.CArea = CA 'Pass ClsArea obj to ClsVolume2
   
   Vol.dblHeight = 10 'assign height to ClsVolume2
   
   
   Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
   With Vol.CArea
     Debug.Print .strDesc, .dblLength, .dblWidth, .Area(), Vol.dblHeight, Vol.Volume()
   End With
   Stop
   
   Set CA = Nothing
   Set Vol = Nothing
   
   End Sub
   

   In the first Dim statement CA is defined as ClsArea Object and Vol as ClsVolume2 Object.  Next two statements instantiates both objects in memory.

   Next three statements  assigns values into the properties of ClsArea Class Object.

   The Stop statement gives a pause in the Code execution, so that we can verify the Object Property values in the Locals Window.

   The Set Vol.CArea = CA statement assigns the ClsArea Class Object CA, as a child object into the Vol (ClsVolume2) Object. 

   In the Next step dblHeight Property of ClsVolume2 Class Object is assigned with the value 10.

   The next statements before the Stop statement prints the Values from memory to the Debug Window.

   Next two Set Statements removes the Objects from memory, before ending the program.

2. Select Locals Window Option from the View Menu.
3. Click somewhere in the middle of the Code and press F5 to run the code till the program pauses at the Stop statement. Alternatively you can press F8 to run the code one step at a time to inspect the Locals Window for changes, at each step.
4. Click on the [+] Symbol to expand and display both Objects Properties and values.
5. Check the CArea and p_Area Object reference in the Value column of the Vol Object.  The Value in there is showing as Nothing because we have not yet passed CA Object to the Vol Object.
6. If you have finished viewing the Locals Window contents then run the code till the next Stop statement.  Now, the CArea Get Property Procedure and p_Area Object are assigned with the ClsArea Class Object.

We will try another Variant example of both these two Classes ClsArea and ClsVolume2.

1.  Insert a new Class Module and change it’s name Property Value to ClsVolume3.

2.  Copy and Paste the following VBA Code into the ClsVolume3 Class Module:

Option Compare Database
Option Explicit
'Method three 
Private p_Height As Double
Public p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = p_Area.dblLength * p_Area.dblWidth * Me.dblHeight
End Function

Private Sub Class_Initialize()
    Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
    Set p_Area = Nothing
End Sub

Check the Code from the beginning: p_Height declared as Private property. The p_Area Property of ClsVolume3 Class declared as Public ClsArea Object. That means p_Area will appear as a Property of the ClsVolume3 Class with it's own displayable properties for direct Get/Let operations in the User Program in Standard Module. Even though ClsArea Class Object has been declared as Public Property of ClsVolume3 Class, it’s Properties are encapsulated in ClsArea Class itself.

Check the Class_Initialize() and Class_Terminate() Sub-Routines. The ClsArea Object is instantiated in the Class_Initialize() Code and removes the Object from memory in Class_Terminate() Code, when the user-program ends.

The sample Test VBA Code is given below.

Copy and Paste the Code into the Standard Module.

Public Sub SNewVol3()
'Here ClsArea class is declared as a Public Property of ClsVolume3
Dim volm As ClsVolume3

Set volm = New ClsVolume3

volm.p_Area.strDesc = "Bed Room"
volm.p_Area.dblLength = 15 'assign length
volm.p_Area.dblWidth = 10 'assign width in clsArea
volm.dblHeight = 10 'assign height to ClsVolume2

Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
With volm.p_Area
   Debug.Print .strDesc, .dblLength, .dblWidth, .Area, volm.dblHeight, volm.Volume
End With
Set volm = Nothing

End Sub

Display the Locals Window (View - -> Locals Window), if it is not already open.

Click somewhere in the middle of the code and press F8 to execute the VBA Code one line at a time and watch the Local Window to track what happens at each step.

In all the above variants of the ClsVolume Class have been written with less Code, except the first example of ClsVolume Class.  

Next week we will work with a built-in Object DAO.Recordset and build a Class Module to:

1. Calculate and update a Field,
2. Sort the Data,
3. Print the sorted data into the Debug Window,
4. and Create a Clone of the Table with Sorted data.

That is lot of actions next week.

• Function Parameter ByVal and ByRef Usage
• Function Parameter Array Passing
• Passing Two Dimensional Arrays
• User-Defined Data Type-2
• Opening Multiple Instances of Form