Ms-Access VBA Class Object Arrays

Introduction.

Last week, we introduced a simple custom class object with two properties—Length and Width—and a method to calculate the Area of any item using these values. I hope you now have a good understanding of the basics of an MS Access custom class object. If not, please review this page before continuing: MS Access Class Module and VBA.

Class module objects can significantly reduce the amount of repetitive code in your programs. Whenever you find yourself writing the same logic repeatedly, consider whether it could be encapsulated in a class module. (Of course, some repetitive code may also be handled with utility functions, but class objects require a different approach.)

Creating a class object may seem like extra work, as refining its functionality often requires more code initially. However, your main modules remain simpler, while the detailed and more complex code stays hidden inside the class—organized, reusable, and easier to maintain.

For now, we’ll build on our simple class module and extend it to create an array of objects and calculate the area of multiple items.

The ClassArray() Sub-Routine.

The following sample VBA code, placed in a Standard Module, creates an array of five objects from the ClsArea class. It then prints their property values and method results in the Debug Window.

You can copy and paste the code into a standard module, but it’s even better to type it out yourself—this way, you’ll understand each line and what it does more thoroughly.

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’s examine the code line by line. In the declarations area, we first declare an object variable:

Dim tmpA As ClsArea

The next line declares an empty array of the same type:

Dim CA() As ClsArea 

The array is left un-sized here — we’ll set its length later in the procedure (typically with ReDim) to hold the required number of ClsArea instances.

In the next two lines, the variable title is declared as a String, and the variables j, L, and U are declared as Long integers.

The title variable is then initialized with the string "ClassArray", which will be used as the prompt text in the InputBox() function.

The For… Next loop is set up with j as the control variable, running five times. After the next two comment lines, the Set statement is used to instantiate (allocate memory for) the ClsArea class object, assigning it to the temporary object variable tmpA.

The following three lines of code assign input values to the strDesc, dblLength, and dblWidth properties of the tmpA object.

After another comment line, the ClsArea object array (CA) is resized with the ReDim statement to hold elements from 1 to j. On the first pass, this creates space for element 1; on subsequent passes, the array is resized to hold elements 1 to 2, 1 to 3, and so on, up to 1 to 5. The Preserve keyword ensures that previously created objects in the array are retained while expanding its size.

Note: The ReDim statement changes the size of the object array, but without the Preserve keyword, it erases any objects already stored in the array. Using Preserve ensures that existing objects remain intact when the array is resized.

The Set statement in the next line copies the tmpA object, along with its data, into the CA(j) object, the newly created j-th element of the array.

Immediately afterward, Set tmpA = Nothing removes the temporary object from memory.

The For…Next loop then repeats this process four more times, allowing input for additional items. Each time, a new tmpA object is created, populated with data, and then stored in the CA array.

After the loop, two lines of code determine the lower and upper bounds of the array (the smallest and largest index values).

Next, a Debug.Print statement prints a heading line in 14-character-wide columns in the Debug window. A second For…Next loop then iterates through the array, using the LBound and UBound values to access each object.

Inside this loop, a With…End With structure is used to simplify referencing the current object (CA(j)). This avoids repeatedly writing CA(j).strDesc, CA(j).dblLength, CA(j).dblWidth, and CA(j).Area. Instead, the property values and the Area() method result are printed more concisely.

Finally, you can remove the comment marker from the Stop statement to pause the program during execution. Run the code again, enter five sets of item details, and experiment with the object array. You can access or modify any property of any object in the array by referencing it with its index number, and you can print the results in the Debug window as shown.

Right-click to open Large Image in New Tab/Window.

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 also pass the Class Object Array as a parameter to another program, similar to how we worked with a User-Defined Data Type.

To demonstrate this, let’s separate the printing section of the code from the main program and move it into its own subroutine.

1. Copy the ClassArray() subroutine code.

2. Paste it into the same Standard Module.

3. Rename the new procedure to ClassArray2(), as shown below.

This new subroutine will handle the printing logic independently, while still working with the array of class objects that we pass to it.

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
 

In the next step, we will separate the printing logic into its own subroutine. To do this, highlight the printing section of the ClassArray2() program, up to the Stop statement, cut it, and paste it below the Dim statements in a new subroutine named ClassPrint(). The revised versions of both programs are shown 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

Review the code changes in both programs carefully and make corrections wherever necessary. Once ready, place the cursor in the middle of the ClassArray2() code and press F5 to run the program.

In this version, the Call statement in the first program passes the CA Class Object Array as a ByRef parameter to the ClassPrint() subroutine. The subroutine then prints the object properties and invokes the Area() function to calculate and display the results.

In the next session, we will extend our ClsArea class module by using it as a base class to build a new VBA Class Object that calculates the volume of a given item.

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