In this blog post we will be discussing collections and how they can be used in VBA.

Sub carParts()
    'A collection is an object that has the ability
    'to store other objects.
    'Collections have 4 methods:
    'Add: Used to add an item to the collection
    'Remove: Used to remove an item from the collection
    'Count: Used to get the number of items in the Collection
    'Item: Used to reference an item in the collection
    'We will be using all of the above methods in our code
    Dim parts As New Collection
    'First we create a collection
    Dim part As Variant
    parts.Add "Volvo"
    parts.Add 5
    parts.Add "V70R"
    parts.Add "Window"
    parts.Add "Drive"
    parts.Add #12/24/2012#
    'We use the Add method to add
    '6 items to the collection
    For Each part In parts
        Debug.Print part, TypeName(part)
        'Here we print each item in the collection
        'to the immediate window
        'we are also printing the type (String, integer)
        'of the item
    Debug.Print "You have " & parts.Count & " parts"
    'We now use the count method to return the number
    'of items in the collection
    'Prints a blank line
    Debug.Print "We will now be removing " & parts.Item(4)
    'We use the Item Method to print the 4th item in the
    parts.Remove (4)
    'We use the Remove method to remove the 4th item
    'from the collection
    For Each part In parts
        Debug.Print part, TypeName(part)
        'Same again
    Debug.Print "You now have " & parts.Count & " parts"
    'Same again again
End Sub

The output of the code above will be:

Volvo String
5 Integer
V70R String
Window String
Drive String
24/12/2012 Date
You have 6 parts

We will now be removing Window
Volvo String
5 Integer
V70R String
Drive String
24/12/2012 Date
You now have 5 parts

Iterating Over The AllForms Collection

Access has a lot of built in collections. One of them is the AllForms collection that contains the names of all the forms in your database.

Public Sub allForms()
Dim form As Variant
For Each form In CurrentProject.allForms
'Here we reference the AllForms collection which
'lists all forms in the database
    Debug.Print form.Name
    'Prints the form name to the immediate window
End Sub

Try it in your own database.

Collections are quite a handy way of storing multiple objects and have 4 useful methods that can be invoked.


Related Posts

Loops – For Each
Working With Form Objects

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.

In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!