Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

VBA Base Class and Derived Object-2


Last week we have created a Derived Class ClsVolume Object, using Class ClsArea as Base Class.  We have created Property Procedures in the Derived Class to expose the Base Class’s Properties and Function to the Object user programs.  This method demands repetition of all the property procedures of the Base Class in the derived class too.  Here, we explore how to create the same Derived ClsVolume Class without repeating the Property Procedures of the Base ClsArea Class.

We have learned the use of the Get and Let Property Procedures in Classes.  There is one more Property Procedure used in Classes:  The Set Property Procedure.  The Set Property Procedure directly assigns an Object to a Class Object of the same Type.

Before continuing further you may visit the earlier pages on this topic, if you have not already done so, the links are given below:

ClsVolume Class, the Makeover.

We shall create a different variant of the same ClsVolume Class Module, that we have created last week, using ClsArea as Base Class, with a different approach, and with less Code. 

Create a new Class Module and change its Name Property Value to ClsVolume2.

Copy and Paste the following Code into the Class Module ClsVolume2 and Save the Module:

Option Compare Database
Option Explicit
'Method two-1
Private p_Height As Double
Private 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 * p_Height
End Function

The new Get and Set Property Procedure for ClsArea Object.

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

Select Compile Project Name from Debug Menu to compile the VBA Code in the Database to ensure that all VBA Project Code is Err Free.  If you have encountered an Error in your other VBA Programs, please track down the error, correct it and recompile your Project.  Otherwise, the VBA IntelliSense that displays a list of properties and functions of Objects will not work.  That will not prevent us from assigning/retrieving values to/from Object Properties.  But, while learning it is important to see the Properties of an Object pop up and displays the list, as an assistant in Coding.

Get / Set instead of the Get / Let Property Procedure.

We have omitted all property procedures of ClsArea, created in the last version of ClsVolume Class, and replaced them with a Get/Set Property Procedures, instead of the Get / Let. Check the declaration Area where we have declared p_Area is declared as a ClsArea Class Object.

When an Object is declared is in this way we normally should create an instance of this object in the Class_Initialize() Procedure in the above Code. We didn't do it here because we plan to do it in the user Program and fill up its Properties with appropriate values and then pass it to the ClsVolume2 Class, before the final calculation phase to use their values.

Take note of the Set CArea() procedure. Its ByRef parameter AreaValue is declared as the ClsArea Object. It will accept the ClsArea Class Object when passed to the Property Set CArea(ByRef AreaValue as ClsArea), in object variable AreaValue, and assigns to the p_Area Property of ClsVolume2 Object.

The Get CArea() Property procedure returns the Object to the calling program.

In our earlier programs, we have written Property procedures for individual elements (Length, Width, Height) of an object to assign/return values To/From them.  Here, the difference is that we are passing an entire Object as a Parameter to the Set Procedure. To retrieve this Object's Property Value (say lblHeight) we must address it as CArea.dblLength. The Get/Set Property Procedure name CArea becomes the child object of the main Object when declared in the Main Program and their Property Procedures can be accessed directly through the Object Address Vol.CArea.dblLength.

A Test Program in Standard Module.

We will write a small program in the Standard Module to test our newly derived Class Object ClsVolume2.

Insert a new Standard Module in your Project. Copy and paste the following Code into the Module and Save the code:

Public Sub SetNewVol2_1()
'Method 1/2
Dim Vol As New ClsVolume2

'ClsArea Object instantiated and passed to the
'Property Procedure Set CArea in ClsVolume2.

Set Vol.CArea = New ClsArea 'declare and instantiate the object in one statement


Vol.CArea.strDesc = "Bed Room"
Vol.CArea.dblLength = 90
Vol.CArea.dblWidth = 10

Vol.dblHeight = 10 'assign height to ClsVolume2


Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
Debug.Print Vol.CArea.strDesc, Vol.CArea.dblLength, Vol.CArea.dblWidth, Vol.CArea.Area, Vol.dblHeight, Vol.Volume

Set Vol.CArea = Nothing
Set Vol = Nothing

End Sub

Code Review Line by Line.

.Let us take a quick look at the VBA code above. First-line instantiates the Class ClsVolume2 with the name Vol. After the next two comment lines the Set statement with the Vol.CArea Property Procedure is called and passes the New instantiated ClsArea Object as the Parameter.

I put a Stop statement on the next line to give a pause in the Program to see how the object is being assigned to the Set CArea Object.  How to do that, We will explore that shortly?

The next four lines assign values to the ClsArea Object and to the Height property of the ClsVolume2 Object.

The next Stop creates a pause in the Program so that we can inspect the memory of how the values are kept in memory.

Next line prints the Headings in the Debug Window for the values printed on the next line.

Next line prints the values of Object Properties from memory in the Debug Window.

Run the Code to the Next Stop Statement

Let us run the Code and inspect the memory to see what happens there at each stage, where I put the Stop statement.

  1. Click somewhere in the middle of the code and press F5 to run the code and pause the program at the first Stop Statement.
  2. Select Locals Window from the View Menu to open a new window below the Code Window, to display how the ClsArea and ClsVolume2 Objects, their properties, and their member property procedures are held in memory.  A sample image of the Locals Window is given below.

    The Locals Window View.

  3. Drag other Windows' sizing handles up to reduce their height to give more space for the display of Locals Window.  Better, close Debug Window, for the time being, and use Ctrl+G to bring it back when needed later.

    We can have a graphical view of all the objects and their Properties in the Locals Window.  The first name with the plus [+] symbol shows the name of the Standard Module, from where our program is running.

    The next plus [+] symbol with the name Vol is the ClsVolume2 instantiated Object in memory.

  4. Click on the [+] symbols to expand and display the details.

    You will find the next level of Objects and Properties.

    The [+]CArea indicates that this Object has the next level of Properties and their Values.

    The dblHeight Get property Procedure comes directly under the Vol Object.

    The [+]p_Area is the Private Property declared ClsArea Class in the ClsVolume2 Class.

    The p_Height is also the Private Property declared in the ClsVolume2.

  5. Click on the plus [+] symbols to expand the objects to show their Properties and Values.

    The expansion of  [+]CArea gives us the view of the ClsArea Object we have passed to the Set CArea() property procedure.

    The expansion of [+]p_Area gives the view of the ClsArea Property declared as Private.

    Note the p_Area Private Property, of ClsVolume2 Class Object, and all its elements are accessible only through the CArea Object Property Get/Set Procedures to the outside world.

    The second column of the Locals window will show the values assigned to the Object Properties and currently no values in them.

    The Third Column shows the Data Type or Object Class Module Names.

  6. Press F5 to run the program further, till it is paused at the next Stop statement, to assign some values to the Object Properties.  The program will pause at the next Stop statement.  Check the Locals Window for changes in Values.

Inside the CArea Object the first two lines with values 90, 10, and the last strDesc Variable with value "Bed Room" are assigned through the Get Property Procedures respectively. The p_Desc, p_Length, and p_width are values assigned through Set Property Procedures to p_Area Property of ClsVolume2 Class Object as well.

The p_Area Object of ClsArea Class declared as Private Property of ClsVolume2 is seen with its Get/Set Property Procedures and assigned values.

Check the Type Column of [-]CArea and [-]p_Area both Objects are derived from ClsArea Base Class.

Usage of ClsArea and ClsVolume2 Class Objects Differently.

Next week we will try another approach with the same two objects.  If you want to try it yourself, here is the clue as to how to try it out yourself.

  1. Instantiate ClsVolume2 and ClsArea Class as two different Objects in the Standard Module Program.
  2. Assign values into both Object Properties.
  3. Assign the ClsArea instantiated Object to the CArea Object in ClsVolume2 Class Object, before printing the Values to the Debug Window.

In this example, we can achieve the same result as we did in the above example, without repeating the Get/Let Property Procedures as we did in the ClsVolume Class Module.

The Links of All the Pages in this Topic.

  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. Add Class Objects as Dictionary Items
  18. Update Class Object Dictionary Item on Form



  1. Thanks for your articles, it makes very interesting reading.

    One thing I would like to point out is this:

    Public Property Set CArea(ByRef AreaValue As ClsArea)
    Set p_Area = AreaValue
    End Property

    Normally if you omit the keyword, ByRef or ByVal, the default is ByRef.

    However, I have noticed for Let and Set Properties, even if you add ByRef, it still behaves ByVal!

  2. Set Property Procedure Assigns Object to an Object Variable. The Normal parameter usage is ByRef for Objects and Arrays. There are some places where I made some mistakes by Copy Pasting Code from earlier Articles and overlooked the corrections in some cases on these pages. Explicit ByVal parameter usage for Class Object didn't have any side effects in the Set Property Procedure. It appears that the ByVal clause is ignored by the System and the code worked correctly.

    I will go through the Pages once again and will make correction.

    Thanks for pointing out the mistakes.

    1. For Objects, Arrays etc. need their location addresses passed to Functions, Sub-Routines or Property Procedures. You can pass the value of an array element to a function with ByVal, but not the entire Array. It needs to be passed by ByRef only, either by implicit or explicit reference.

      In the above case, where ByVal is used for a Class Object parameter, by mistake, the explicit ByVal usage is ignored for an Object and took it as ByRef by the System. Otherwise we will not get access to the Object Properties or Methods.


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code