Ms-Access VBA Class Object Arrays

Introduction.

Last week we briefly introduced a simple Custom Class Object with only two Properties, Length, and Width Values. A Method for calculating the Area of anything, with Length and Width Values. Hope you understood the basics of a Ms-Access Custom Class Object.  If not, here is the link: Ms-Access Class Module and VBA. Please visit the page before continuing. Class Module Objects save 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
  Erase CA


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 the Keyboard for 5 different Items, one after the other.

The 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, declared the variable's title the 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 as 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  elements  (1 to 1 time) by preserving the existing Object elements, if any (this is the first Object in the Array).  This will keep incrementing, through the ReDim statement, for 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 erased.

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 j^th 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 in the CA Object Array.

In 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 an 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() result in 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 address any of the Object properties 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 of 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 into the same Standard Module, and change the name to 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
Erase CA

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 the Code, and paste it below the Dim statements in the ClassPrint() Program.  The modified version of both Codes is 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

Erase CA

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 make corrections, wherever applicable.  Click in the middle of the 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