Ms-Access and Collection Object Basics

Collection Object Basics.

A Collection object is a built-in VBA object that provides a convenient way to store and manage a group of related objects or values as a single unit. It is available in all Microsoft Office applications that support VBA, including Microsoft Access, Excel, Word, Outlook, and PowerPoint.

Unlike an array, a Collection is dynamic, meaning that items can be added or removed at run time without requiring the collection to be resized. Each item in the Collection is automatically assigned a numeric index, and it may also be assigned a unique string key for direct retrieval.

Key Characteristics

• Dynamic storage – Items can be added or removed at any time during program execution.

• Ordered collection – Items are maintained in the order in which they are added unless inserted at a specific position.

• Heterogeneous contents – A Collection can contain objects, variables, or values of different data types.

• Indexed access – Items can be accessed by their numeric position (1-based indexing).

• Key-based access – Items may optionally be assigned unique string keys for faster identification.

• Automatic memory management – VBA manages the internal storage of Collection, eliminating the need for manual resizing.

Typical Uses

The Collection object is widely used to:

• Store instances of custom class objects.
• Maintain lists of Forms or Controls.
• Organize records or business objects in memory.
• Build reusable object frameworks.
• Manage wrapper class instances in Microsoft Access applications.
• Replace dynamically resized arrays where frequent additions or deletions are required.

In VBA, Arrays are more commonly used than Collection objects for storing multiple sets of related values (in rows and columns). We have already used Arrays to store User-Defined Types and Class Module objects. Now, it’s time to explore something new—the use of Collection and Dictionary objects. The Collection object is particularly convenient for grouping related items together. As for the Dictionary object, we will discuss its usage at the appropriate time.

When using Arrays, we must dimension a variable, User-Defined Type, or Class Module object with the required number of elements in advance—or re-dimension it later to increase or decrease its size—before storing values in it. This extra step is not necessary with a Collection object. Once a Collection object is instantiated, we can dynamically add any number of items to it. Its members can be of any data type, including built-in objects, Class Module objects, or even other Collection objects with their own item members.

Collection Object Demo Program.

Let us write a simple program to demonstrate the usage of a Collection Object.

Public Sub CollTest1()
Dim C As Collection
Dim j As Integer

‘instantiate the Object
Set C = New Collection
                            
C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

'Print the items in debug window
GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
Debug.Print
For j = 1 To C.Count
    Debug.Print C.Item(j)
Next
Return

End Sub

Code Review Line By Line.

The first two lines declare the Variable C as a Collection Object. The next line declares the Variable j as an Integer type, as a control variable for the For... Next Loop.  The third line instantiates the Collection Object C in memory.

The Collection Object has four built-in methods: Add, Count, Item, and Remove, for managing the Collection items in memory.

An image of the Collection Object instance below, displaying its list of methods.

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

• The Add method adds a new Item to the Collection.  The Remove method removes an item by its numeric index or key.

• To access a specific item, we can use the Item method with its index number. The Count property returns the total number of items in the Collection.

• In the current example, we are not working with object-type items but with a few simple mixed data types—Integer, String, and Double—as members of the Collection. Using the Add method of the Collection object named C, we have inserted five items: the first two are integers, the next two are strings, and the last one is a double-precision number.

• The syntax: C.Add (Item, [Key], [Before], [After])  The Add method is followed by a space, and then the actual value to be added. Other parameters are optional.

• The Add method accepts four optional parameters, as shown in the image below.

  

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

• The parameters: Item, [Key], [Before], [After].  The first parameter, Item, is mandatory; the Value to be added to the Collection.

• The next three parameters are optional. 

• When using any of the optional parameters of the Add method, you must insert comma placeholders to skip over unused parameters—except when you are specifying parameters from the rightmost side in order.

  Alternatively, you can explicitly use parameter names with their values, which allows you to provide the parameters in any order.

  We will explore this technique in another VBA example.

  Note: Do not misinterpret the Item Parameter of the Add Method with the Item() Method of the Collection Object.

We have added five items as Collection Object members with the Add method.  Two integer-type values, two String-type Values, and one double-precision number. 

It demonstrates that you can add any data type, except User-Defined Type (UDTs), into the Collection.  When you want to add UDTs into a Collection, convert your UDTs into a Class Module Object.

Next, the program calls a printing subroutine that outputs the Collection members to the Debug window. This subroutine uses a For...Next loop that runs from 1 to the total number of items (C.Count) in the Collection. The loop control variable j is used as the index parameter of the Collection’s Item() method to retrieve each value and print it to the Debug window.

The next Line removes the third item (iPhone) from the item members by calling the Remove method.

The printing subroutine is called one more time to print the list of items, after removing the third item from the earlier list.

The Exit Sub statement prevents the program control from dropping into the internal subroutine lines and stops the program.  The listing will appear in the Debug Window as shown below.

The Output in the Debug Window.

5 
15 
iPhone
Disk 2TB
35.75

5 
15 
Disk 2TB
35.75

We can insert a value before a particular item member by specifying the item number with the Before key Name.

C.Add 2, Before:=1 ‘add value 2 Before existing first item 
OR
C.Add 2,,1

The above statement will add value 2 as the first item in the above program, pushing all existing items down.

C.Add 20, After:=3 ‘Add value 20 After existing item number 3
OR
C.Add 20,,,3

This statement inserts the value 20 after the third item, after value 15,  in the list.

The Code below demonstrates the Before:= and After:= Parameter Names.

Public Sub CollTest2()
Dim C As Collection
Dim j As Integer

Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, Before:=1 'Insert the item before the first item
C.Add 20, After:=3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing
Exit Sub

Listing:
'Print the items
Debug.Print
For j = 1 To C.Count
   Debug.Print C(j)

Next
Return

End Sub

Note: By using parameter names, you can pass values in any order you want when you need to use more than one parameter in a statement.

C.Add After:=3,Item:=20

The second example demonstrates parameter use without explicitly specifying their parameter names, placing each value in its defined correct positional order.

Public Sub CollTest2_2()
Dim C As Collection
Dim k As Integer
Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, , 1 'Insert the item before the first item
C.Add 20, , , 3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
'Print the items
Debug.Print
     For k = 1 To C.Count
         Debug.Print C(k)
     Next: Debug.Print
Return
End Sub

Sample printout in the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75 

In all our printing examples, we have used the Item’s index number to retrieve values for display in the Debug Window. However, when a collection contains many items, it becomes difficult to remember the index number of a specific item we want to access. To overcome this, we can associate each item with an easily memorable Key along with its Value—for example, using a contact’s first name as the Key in an address book Collection—so we can retrieve the item’s value directly by its Key instead of relying on its index number.

Usage of Item Keys.

Let’s write a new program to demonstrate how to use Keys with Values in a Collection.

Public Sub CollTest3()
Dim C As Collection
Dim strKey As String
Dim strGet As String

Set C = New Collection

C.Add 5, Key:="FIVE" 
C.Add 15, Key:="FIFTEEN"
C.Add "iPhone", "7+"     'you can omit the KEY param name
C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY
C.Add 35.75, "999"

'add value 2 with Key "TWO" before the first item.
'this item will be the first item in the collection
'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO"

'add value 7 with Key "SEVEN" as third item in the collection
'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2

'Retrieve value using it's KEY from collection

strKey = ""
Do While strKey = ""
    strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "")
    
    Select Case strKey
        Case "Q"
           Exit Do
        Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999"
           strGet = C(strKey)
        Case Else
           strGet = " Not Found!"
   End Select
   
MsgBox "Key:<<" & strKey & ">> Value: " & strGet
strKey = ""
Loop

Set C = Nothing

End Sub

The KEY value must be of String Type.  The KEY value must be a unique identifier.

Refer to the second image on this page as a reference to view the proper order of the Parameters of the Collection Object Add Method, displayed by VBA Intellisense.

The sample programs shown earlier, with mixed data types, were intended solely for demonstration purposes. In practical use, a Collection object typically stores items of a single data type, most commonly used to hold objects—such as Forms, Reports, Class Module objects, or Database objects—along with their associated properties and methods.

CLASS MODULES.

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

COLLECTION OBJECT.

1. MS-Access and Collection Object Basics
2. MS-Access Class Module and Collection Object
3. Table Records in Collection Object and Form

DICTIONARY OBJECT.

1. Dictionary Object Basics
2. Dictionary Object Basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary to Form
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item on Form