Introduction.
Last week we had a brief introduction to the Collection Object Basics. We have learned how to Add the items to a Collection Object and the data item retrieval methods. We have used mixed data types as Collection Items in our examples, and they were intended for demonstration purposes only.
The Collection Object Items will have only one type of value or can have a collection of other Objects with their Properties.
We have familiarized ourselves with the following Collection Object Methods earlier:
- Add – to add an Item to the Collection Object
- Count – returns the Count of Total Items within the Collection Object
- Item – Takes the item sequence number as a parameter and returns a specific Item from the Collection.
- Remove – Takes the item number as a parameter and deletes the item from the Collection list.
The Add method has four parameters: Item, Key, Before, and After, and we have learned their usage too.
Now, let us proceed further with the real-world usage of Collection Object with a few examples.
The sample VBA Code given below uses the first Ms-Access Class Object ClsArea, which we created earlier, for calculating the Area of rectangular Rooms, Objects, etc. We have created an Array of Class Module Object ClsArea and worked with them to calculate the area of several Rooms.
Now, it is time to learn the usage of Collection Object, instead of Arrays, to create and add several room details into the Collection Object. The items can be retrieved, when needed, and print the details in the Debug Window.
Adding Class Object to the Collection.
- Open your database and display the VBA Window (ALT+F11).
- Copy and paste the following VBA Code into a Standard Module:
Public Sub Coll_TestOne() Dim Arr As ClsArea Dim objColl As Collection, obj As Object Dim j As Integer, iMax As Integer Dim strName As String iMax = 5 'instantiate the Collection object in memory Set objColl = New Collection 'Create a Collection of five ClsArea Object 'with it's Property values filled-in For j = 1 To iMax Set Arr = New ClsArea strName = "Bed Room" & j Arr.strDesc = strName Arr.dblLength = 20 + j Arr.dblWidth = 15 + j objColl.Add Arr 'Add current Class Object to Collection Set Arr = Nothing Next 'Printing Section Debug.Print "Description", "Length", "Width", "Area" For Each obj In objColl With obj Debug.Print .strDesc, .dblLength, .dblWidth, .Area End With Next Set objColl = Nothing End Sub
The VBA Code Line By Line.
Before running the Code, let us take a look at each line and see what it does.
First-line declares Arr as ClsArea Object. The next line declares ObjColl as a Collection Object and Obj as a generic Object type. The next line declares two Integer type variables and the line next to that declares a String variable.
The iMax variable is initialized with the value 5 for For . . . Next Loop control purposes. We will create five ClsArea Objects and their property values will be assigned before adding them to the Collection Object, one by one.
The next executable line instantiates the Collection Object ObjColl in memory.
Next, a For . . . Next Loop is set up to run for 1 to iMax (or 5) times. The Arr is instantiated as a ClsArea Class Object, in memory, in preparation for assigning the first ‘Bed Room1’ Property Values. The room Description will be created by the constant value Bed Room and with the control variable j, value to the name of the Rooms, as ‘Bed Room1’ to ‘Bed Room5’.
Similar way, the dblLength Property value of the room will be 20+j (or 21 to 25) and the dblWidth Property value will be 15+j (16 to 20).
After assigning the Arr property values the Class Object is added to the Collection ObjColl with the Add Method.
The Set Arr = Nothing statement removes the current Class object from memory.
The For . . . Next Loop repeats to Add four more items in the Collection.
Next, the For. . . Next Loop, in the Printing Section, uses the Obj Object variable as a control to read each item from the Collection and prints the ClsArea Object Property Values in the Debug Window.
- Click somewhere in the middle of the Code.
- Press CTRL+G to display the Debug Window.
- Click somewhere in the code and press F5 Key to run the Code.
The output in the debug window will be as shown below:
Description Length Width Area Bed Room1 21 16 336 Bed Room2 22 17 374 Bed Room3 23 18 414 Bed Room4 24 19 456 Bed Room5 25 20 500
We have not entered the second parameter Key Value in the above example. The printing section uses an object variable to retrieve each ClsArea Class Object item, in the same order, as they were added to the Collection Object, then prints its property values in the Debug Window.
The following code snippet can be replaced, in the above code, to retrieve each item from the Collection Object. This will use the item sequence number, as a parameter to the Item Method to retrieve each object, in the same order as they were added to the Collection.
'Printing Section Debug.Print "Description", "Length", "Width", "Area" For j = 1 To objColl.Count With objColl.item(j) Debug.Print .strDesc, .dblLength, .dblWidth, .Area End With Next
The number of items in the collection is determined by the expression ObjColl.Count in the For . . ..Next Loop.
You may insert a Stop statement above the Printing Section and run the above code again. When the program pauses execution of the Stop statement, select Locals Window from the View Menu.
Click on the plus symbol [+]ObjColl to display ClsArea Object Items within the Collection Object.
Click on the plus symbol [+]Item 1 to expand the item and display the ClsArea Object Properties and their Values.
Note the dblLength property value is shown as 21. We will try to change this value by typing an expression in the Debug Window. Type the following expression directly in the Debug Window and press Enter Key:
ObjColl.Item(1).dblLength = 50
Check the Locals Window now, the value 21 is now changed to 50. You can overwrite the Item Property Values but cannot replace the object item directly.
If you would like to overwrite an object item, then you must Remove the wrong item first from the Collection, with the item sequence number as a parameter (say Remove 1), and add a new object with whatever changes are required to the new item. You may use the After or Before parameter with the proper item number to position the item in the exact area in the Collection Object, from where you have removed the old item. Check last week’s Article: Ms-Access and Collection Object Basics for clarity on Before or After parameter usage of Add Method.
Save to Collection with Key.
Let us try another example, with a few changes to the above code, to Add items with the KEY value in the Collection Object. In the printing section, we will retrieve ClsArea Class Object items from Collection with the use of Key values.
Copy and Paste the following Code into a Standard Module:
Public Sub Coll_TestTwo() Dim Arr As ClsArea Dim objColl As Collection Dim j As Integer, obj As Object Dim iMax As Integer Dim Desc As String, strKey As String iMax = 5 'instantiate Collection Object Set objColl = New Collection For j = 1 To iMax 'instantiate a temporary ClsArea Object Set Arr = New ClsArea Desc = "Bed Room" & j 'Assign Property Values Arr.strDesc = Desc Arr.dblLength = 20 + j Arr.dblWidth = 15 + j On Error Resume Next 'Validate Collection Key, ClsArea Object with the same Key exists or not Set obj = objColl.Item(Desc) If obj Is Nothing Then ' doesn't exists, Add the ClsArea Instance objColl.Add Arr, Desc 'Add Item to Collection with KEY parameter Else MsgBox "Error: " & Desc & " Already Exists." End If On Error GoTo 0 Set Arr = Nothing 'release temporary ClsArea Object Next 'Print items in reverse order Debug.Print For j = objColl.Count To 1 Step -1 strKey = "Bed Room" & j Set obj = objColl.Item(strKey) 'access items with KEY value With obj Debug.Print .strDesc, .dblLength, .dblWidth, .Area End With Next Set objColl = Nothing End Sub
We have declared two more String Variables (Desc) to store the Item Description for ClsArea Object instance property Arr.strDesc. The same description will be used as Key-value, in the Add method too.
The strKey variable will be used to create the Key-Value in the printing section for retrieving each item object from the Collection. We can re-use the Desc variable in the printing Section also, for creating the Key-Value, but a separate variable strKey is used for clarity.
The objColl.Add Arr, Desc statement adds the ClsArea Object instance Arr into the Collection Object Item, with the Description (Bed Room1) in Desc Variable as Key-Value to the Collection Object, as the first Item.
Other Syntax variants of the above statement are given below for reference.
objColl.Add item:=Arr, key:=Desc 'statement with Parameter Names 'OR objColl.Add key:=Desc, item:=Arr 'values in improper order with Parameter Names '
In the Printing Section we have set the For . . . Next Loop’s control sequence in reverse order to retrieve the items using the Key Values for a change, from ‘Bed Room5’ to ‘Bed Room1’ order. The Set ObjColl = Nothing statement clears the Collection Object from memory.
There are a few problems with the Key-Value of Collection Object:
It accepts only String data type as Key-Value.
Once the Key-Value is assigned to a Collection item you cannot retrieve the Key value itself, or create a list of the Keys from the Collection Object.
If you already know the Key-Value (like Employee Code) you can retrieve the actual Object Item belonging to that Key from the Collection.
If you attempt to Add an Item with an existing Key Value in the Collection it will trigger an Error.
A better option is the Dictionary Object. It has much more flexible options to store and retrieve values or Object Items.
We will explore and experiment with the Dictionary Object and its methods next week.
CLASS MODULES.
- MS-Access Class Module and VBA
- MS-Access VBA Class Object Arrays
- MS-Access Base Class and Derived Objects
- VBA Base Class and Derived Objects-2
- Base Class and Derived Object Variants
- Ms-Access Recordset and Class Module
- Access Class Module and Wrapper Classes
- Wrapper Class Functionality Transformation
COLLECTION OBJECT.
- Ms-Access and Collection Object Basics
- Ms-Access Class Module and Collection Object
- Table Records in Collection Object and Form
No comments:
Post a Comment
Comments subject to moderation before publishing.