Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access Base Class and Derived Objects

Introduction.

If you have not seen the earlier Posts on Microsoft Access Class Module, please go through them before continuing, the links are given below.

  1. MS-Access Class Module and VBA.
  2. MS-Access VBA Class Object Array.

The ClsArea Class can perform as a Base Class in another Class Object, the calculation performed by the base class can be used as part of the new object’s calculations. For example, It can be part of an Object that calculates the Volume of something,

The dbl in dblLength and dblWidth Property Procedure Names for Get/Let Procedures are simply an indication that the Class Object expects the Length and width Values in double precision numbers.  Similarly, If we change the Property Procedure Names to Quantity and UnitPrice then the first value multiplied by the second value gives us the Total Price of some item. 

It simply means that you can use the ClsArea Class as a base class, wherever you need the result of the first value multiplied by the second value, like Total Price * Tax Rate to calculate tax amount or Total Price * Discount Rate to find Discount Amount and so on. 

Even though we have developed a simple Class Module it can be part of many other derived Classes.  The possibilities are open to your imagination and creativity.

Our ClsArea Class calculates the area of materials, Rooms, or similar items with Length and Width Properties only.  It doesn't calculate the area of the Triangle or Circle.  But, it can be part of a new Class Object that calculates the Volume of Rooms, Warehouses to find storage capacity.  For that, we need one more value Height of Room, Warehouse, etc.

The Volume Class: ClsVolume.

Let us create a new Class Module ClsVolume, using the ClsArea as Base Class.  Insert a Class Module and change its 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

The ClsVolume Class Object’s Code is not yet complete.  Let us examine the code line-by-line.  In the third line declared as Private Property p_Area of ClsArea Class Object.

The next line declares a Private Property with the name p_Height of data type Double. 

The Class_Initialize() and Class_Terminate() Sub-Routines.

The Next two Sub-Routines (Initialize() and Terminate()) are very important here. 

The Initialize()  runs and instantiates the ClsArea Object in memory when we use and instantiate the ClsVolume Class Object in our Standard Module program.

When we execute the Statement Set ClsVolume = Nothing in the Standard Module program, to clear the ClsVolume Object from memory, the Terminate() Sub-Routine runs and releases the memory space occupied by the ClsArea Object.

The Property Get dblHeight Procedure returns the value from p_Height Property to the calling Program.

The Property Let dblHeight Procedure validates the value passed to the NewValue parameter and assigns it into the private property p_Height.

The Public Function Volume() calculates the Volume, by calling the p_Area.Area() Function and the returned area value is multiplied by p_Height to calculate Volume, with the expression: Volume = p_Area.Area * p_Height.  But, before executing this statement we are performing a validation check to ensure that p_Area.Area() function returns a value greater than zero, indicating that p_Area.dblLength, p_Area.dblWidth Properties have valid values in them and p_Height property value is greater than zero.

Note:  Since, the p_Area Object of Class ClsArea is defined as Private Property of ClsVolume Class we have to make its Properties (strDesc, dblLength, dblWidth and Area() function) visible to the outside world for Get/Let Operations and to return Area Value.  That means we have to define Get/Let Property Procedures for strDesc,  dblLength, dblWidth Properties, and Area() function of ClsArea Class Object in ClsVolume Class Module too. 

The Let/Get Property Procedures.

Add the following Property Get/Let Procedures and Area() function to the ClsVolume Class Module Code:

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 lines of Code.  The usage of Procedure name strDesc is simply a matter of choice, if you want to use a different name you are welcome.  But, the original Property Name we have used in the ClsArea Class is strDesc.  Using that original name here reminds us of the relationship with the original ClsArea Class Object.

In the next Get dblLength() Property Procedure, the expression to the right of the = sign p_Area.dblLength reads the dblLength value stored in the ClsArea Class Object and returns to the calling program.

The Let Property Procedure assigns the parameter value in the NewValue variable to the p_Area.dblLength Property of ClsArea Class Object.  Here, we are not running any validation check on the received value in the NewValue parameter variable.  The Validation check will be performed within the ClsArea Class itself when we assign the value to p_Area.dblLength property.

Similarly, the Get/Let Property Procedures are added for the p_Area.dblWidth Property too,

Next, the p_Area.Area() Function is made visible through the ClsVolume Class Object to the calling program.

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

I know, what you are thinking by now: like “this is double work, it is nice if we can, some way, skip these steps of repeating ClsArea Property Procedures again in the ClsVolume Class ”.  Or say, we could have added the dblWidth Property in the ClsArea itself and run the Area() and Volume() methods from there itself, right?

The whole point here is that how a Base Class Object can become part of designing another Class Object.

Remember, the whole idea of designing a Reusable Class Module Object is that the main programs, using the Class Object, will be simple and the intricacies built into the Class Object remains hidden.

Yes, we can do it more than one way, with a compact code as well.  We will explore them later, but for now, let us continue with our original plan.

The Main Program that Uses the ClsVolume Class.

Let us test our new ClsVolume Class in the main Program in 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.  Press Ctrl+G Keys to display the Debug Window if it is not already in the open state. Click somewhere in the middle of the Code and press F5 Key to run the Code.  The sample output on the Debug Window is 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.

We will run tests to see that the Base Class ClsArea Class’s input value validation check works when values are passed to it through the ClsVolume Class. We have performed some validation checks in the Area() and Volume() functions too.

Let us try them one by one:

First, we will pass a negative value to ClsArea.dblLength property through the ClsVolume Class.  It should trigger the error message and open up the Inputbox() function within the Do While…Loop to input the correct value.

1.  Replace the Value 25, in the line Vol.dblLength = 25,  with –5 and press 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 in the line, replacing –5.

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.

Since there is no input value passed to the Property the Vol.Volume() function will generate an Error saying that all the three Properties: dblLength, dblWidth, and dblHeight, should have values in them to run the Volume function.

Similarly, you may check the Vol.Area() Function’s performance too.

We can create a data printing Function and pass the ClsVolume Object as a parameter to the function and print the values in the Debug Window.

The Changed Code of Main Programs.

The changed Code for both Programs are 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 with less Code.

The Links of All Pages on 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. Update Class Object Dictionary Item on Form

Share:

Ms-Access VBA Class Object Arrays

Introduction.

Last week we had a brief introduction of a simple Custom Class Object with only two Properties, for Length and Width Values. A Method for calculating Area of anything, with Length and Width Values. Hope you understood the basics of a Ms-Access Custom Class Module.  If not, here is the link: Ms-Access Class Module and VBA. Please visit the page before continuing.

Class Module Objects saves a lot of Code in your Programs.  When you found something that you do repeatedly in Code think of a Class Module Object.  The repetitive code can be turned into a utility function too, but for Class Objects, you must take a different approach.

For a simple task, it may take more code to refine the functions of the Class Module Object, but your main module programs will be simple and all the complicated code developed and refined will remain hidden from others.

But for now, we have a simple Class Module in hand we will try how to create an array of Objects to calculate the Area of many items.

The ClassArray() Sub-Routine.

The sample VBA Code in the Standard Module creates an Array of five Objects of ClsArea Class and prints their Property Values and Method Result in the Debug Window.  Copy and Paste (or better if you type them in, to know each line better what they do) the following code into a Standard Module:

Public Sub ClassArray()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim L As Long, U As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
Set tmpA = Nothing 'Remove temporary object from memory.
Next


‘PRINTING SECTION
'Print the Array contents into the Debug Window
L = LBound(CA)
U = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
For j = L To U
   Set CA(j) = Nothing
Next

End Sub
 

Click somewhere in the middle of the Code and press F5 to execute the Code. Input the Values for Description, Length, and Width from Keyboard for 5 different Items, one after the other.

Sample Run of the Program is given below:

Description   Length        Width         Area
Door Mat       5             8             40 
Carpet         12            23            276 
Bed Room       21            23            483 
Store Room     15            25            375 
Terrace        40            50            2000 

Review of Code Line by Line.

Let us examine the code line by line.  In the declaration area, we have declared an Object Variable tmpA of ClsArea Type. In the second line declared an empty Array CA() of ClsArea Type. The required number of elements will be Re-dimensioned later in the program. 

In the next two lines, we have declared variables title of String type and j, L, and U variables of Long Integer type.

Next, the title variable is initialized with the string ‘ClassArray’ and will be used as a Title in the InputBox() Function.  The For….Next loop is set with a control variable j to run the loop five times. After the next two comment lines, the Set statement instantiates (allocates memory) ClsArea Class Object in memory with the name tmpA.

The next three lines of code serve to Input values for strDesc, dblLength, and dblWidth Properties of tmpA Class Object.

After the next line of comment, the ClsArea Class Object (CA) is Re-dimensioned for 1 to j  times (1 to 1 time) by preserving the existing Object elements, if any (this is the first Object in the Array).  This will keep increasing, through the ReDim statement, to 1 to 2, 1 to 3, 1 to 4, and 1 to 5 by preserving the earlier object values, within the For . . . Next loop.  The Preserve keyword ensures that the existing array objects are not lost.

Note: The Re-Dimension statement increase/decreases the number of object elements specified but erases the existing objects loaded into the array earlier, without the Preserve keyword.

The Set statement in the next line copies the tmpA Object, with its data into the CA(j) ClsArea Object newly created jth array element.

Next line Set tmpA = Nothing removes the temporary object from memory.

The For…Next loop repeats this action four more times to input other items into the newly instantiated temporary object tmpA, one after the other, and copies the object into the CA Object Array.

The next two lines, after the comment line, find the Object Array index range (finds the lowest and highest index numbers. 

The next Debug.Print statement prints a heading line in 14 column zones in the Debug Window.   The For . . . Next loop with the L and U bound array index number ranges runs the inner statements to access each Object from Array CA, with index number in control variable j.

The current object reference is set within With. . . End With structure, rather than repeating the Object name CA(j).strDesc, CA(j).dblLength, CA(j).dblWidth and CA(j).Area to print the Object’s Property Values and Method Area() results on the Debug Window.

Remove the comment symbol from the ‘Stop statement to create a pause in the Program on the Stop statement.  Run the code again and enter 5 item details so that you can experiment with the Array Object.   You can selectively address any of the Object Property, with the Array Index number, to edit or print any value on the Debug Window as shown below.


The statement Set CA(j) = Nothing within the For. . . Next Loop clears the array objects, one by one from Memory.

Pass Class Object Array as Function Parameter.

We can pass the Class Object Array as a Parameter to a program, as we did with User-Defined Data Type.

Let us create a simple Printing Subroutine, by cutting the Printing Section Code, of the main program, and placing it into the new program.

Copy the ClassArray() sub-routine Code, Paste it in the same Standard Module and change the name as ClassArray2(), as shown below.

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim L As Long, U As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

     tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
 Set tmpA = Nothing 'Remove temporary object from memory.
Next

‘PRINTING SECTION
'Print the Array contents into the Debug Window
L = LBound(CA)
U = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
For j = L To U
   Set CA(j) = Nothing
Next

End Sub
 

The ClassArray2 Code Without Printing Section.

Create a new Sub-Routine as given below:

Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U As Long
Dim j As Long

End Sub

Highlight the Printing Section up to the ‘Stop statement in the ClassArray2() Program, cut the highlighted area of Code, and paste it below the Dim statements in the ClassPrint() Program.  The modified version of both Codes are given below:

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea
     tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
 Set tmpA = Nothing 'Remove temporary object from memory.
Next

Call ClassPrint(CA) ‘Pass the Object Array to print routine

'Clear array objects from memory
For j = L To U
   Set CA(j) = Nothing
Next

End Sub

The ClassPrint() Program.

Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U As Long
Dim j As Long

'Printing Section
'Print the Array Contents into the Debug Window.
L = LBound(clsPrint)
U = UBound(clsPrint)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With clsPrint(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

End Sub

Check for code changes in both programs and do corrections, wherever applicable.  Click in the middle of ClassArray2() code and press F5 to run the program. 

The Call statement in the first program passes the CA Class Object Array as ByRef Parameter to the ClassPrint() Subroutine.  The program prints the Object Properties and calls the function Area() to calculate and return the value to print.

Next week we will learn how to use our ClsArea Class module as a Base Class to create a new VBA Class Object to calculate the Volume of something.

  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

Share:

MS-Access Class Module and VBA

Introduction.

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

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 of 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 locations list.  Click Office Button –>Access Options—> Trust Center –>Trust Center Settings.  Add the database folder into 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.

  • In the left panel, there is a Name control with the name Class1.
  • Change the name Class1 to ClsArea. This is the name of our Custom Object.

  • Note: The Class Name: ClsArea become 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, below the Option Compare Database and  Option Explicit lines.  Type in the following lines into 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.  Save the Class Module.

    Note: Our Class Module 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 changes. 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 become 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 a Variable as we do for a normal variable, like Dim Desc as String.  But, this is not an ordinary variable we are setting a reference to our Class Module Object ClsArea.  Since it is an object a simple Dimension statement alone is not enough because it will not allocate any memory space to store values into our locally defined Object oArea Properties.

    The Set statement in the next line with the New keyword 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 into 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 it as Private yet. We are on the way towards 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. 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.

    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 Editor Tab.

    Before exiting the Function the last statement should be Set oArea = Nothing. This statement explicitly releases the memory occupied by the instance of the Custom Object, so that more memory is available for other programs.  This is a responsible cleaning up operation by our program.

    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 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 into 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 Private Variables. 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 Procedure and Let Procedure are declared as Public. Both Procedure names are the same strDesc.  By default, the returned data type is Variant in the Get Procedure and the Parameter data type is also inserted as Variant in the Let Procedure.  These we can change to specific types as needed, which we did and changed to String type.  The first three letters str in strDesc gives 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 Get and Let Procedure pairs for a Variable.

    Now, take a look closely at the expression we have written within the Get Procedure.  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 variable p_Desc.

    The point to note here is that there is no direct access to our private variable p_Desc to the outside world.  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
    
    OR
    
    ‘ Reads the value from p_Desc and assigns it to the variable X
    
    X = oArea.strDesc
    
    

    The Let Property Procedure is run when we try to assign a value into the Property Name. Check the example expression in our Test Program below:

    oArea.strDesc = “Carpet”
    
    

    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 into 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 into 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 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 name dblLength and dblWidth to give a hint to the User that these Properties expects 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 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 are 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 Area of Room, Carpet, Floor Tile, or whatever material, which have values of Length and Width.  That means we need a Public Function to calculate the Area of whatever item’s Length, Width and Description Values 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 calls Get Procedures dblLength and dblWidth for calculation.  You might have noticed the reference Me.  used to qualify the dblLength, dblWidth Get Procedures, like we used to write in Form/Report Class Modules, to refer to the current Object in memory and it’s 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 Key Word.  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 in 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 sub-routines 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 are thinking by now, like “so much code for multiplying two variables together”.  It is true in that perspective, but it is very likely that we have written 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 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 get 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 value is entered by the User. 

    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 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 (Me.dblLength > 0) And (Me.dblWidth > 0) Then
            Area = Me.dblLength * Me.dblWidth
      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 (Me.dblLength > 0) And (Me.dblWidth > 0) Then
           Area = Me.dblLength * Me.dblWidth
       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
     'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
    End Sub
    
    Private Sub Class_Terminate()
       'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
    End Sub
    

    Testing Property Procedures and Methods.

    You may test our Custom Class Object by entering negative or 0 values as input to dblLength, dblWidth Properties. 

    In the Test Program Comment 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 Module is now considered complete and we have tested and found it is working correctly. 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 of 5 bed-rooms 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 into each instance of the Object and can work with 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

    Share:

    MSA GURU : Access Tips & Tricks App

    • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

    Want to Post Free Ads on the Web


    Translate



    PageRank
    Subscribe in a reader
    Your email address:

    Delivered by FeedBurner

    Search

    Popular Posts

    Blog Archive

    Powered by Blogger.

    Labels

    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 Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control 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