The Example Workbook

Download and extract the workbook linked to in the Files Needed section above.

Examples of Collections

Many objects in Excel VBA are organised into collections.  A collection refers to all of the objects of a particular type within a specific container.  The example workbook that you have opened is a container for a number of collections:

The workbook contains a collection of five Sheets. There is also a separate collection of three Worksheets, and a separate collection of two Charts.

Each worksheet is a container for other collections:

This worksheet contains a collection of four ListObjects and a collection of four ChartObjects.

Each chart also contains collections of other objects:

A chart contains a SeriesCollection collection. Each Series object in a SeriesCollection is a container for a Points collection.

The workbook itself belongs to a Workbooks collection which contains all of the currently open workbooks.

Referring to Collections

At a basic level, a collection is a special type of VBA object.  As with other VBA objects, you can use methods and properties of a collection to make things happen.  In order to do this, you first need to know how to refer to the collection.  Open the VBE and find the subroutine called ReferToCollections in Module1:

Find this subroutine in Module1.

Write the name of the Workbooks collection followed by a full stop:

The IntelliSense list shows the methods and properties of the Workbooks collection.

Using the list shown in the image above, you could, for example, use the Add method to create a new, blank workbook.  You could use the Close method to close all of the currently open workbooks.  you could use the Count property to return the number of open workbooks.  We'll add some code to write the number of open workbooks into the Immediate window.  Add a Debug.Print statement as shown in the diagram below:

We've added some text so that we know what the number represents when we run the code.

Add more code to print the Count property of the Worksheets, Charts and Sheets collections to the Immediate window:

Feel free to copy, paste and edit the code rather than writing out each line from scratch.

Run the subroutine and check the results in the Immediate window.  If you can't see this window choose View | Immediate Window from the menu:

These are the results you will see if you only have open the workbook that you downloaded earlier.

Using the Default Container for a Collection

In some cases when you refer to a collection, VBA assumes which object is the container for the collection you have referenced.  For example, when you refer to the Worksheets collection, VBA assumes that you are referring to the collection of worksheets in the currently active workbook.  Add a line of code to the top of the ReferToCollections subroutine to create a new workbook:

Add an instruction to add an object to the Workbooks collection.

Adding a new workbook to the Workbooks collection automatically makes the new workbook the active one. Run the subroutine again and check the results in the Immediate window (you may wish to clear the contents of the window first):

The output shows the counts of the collections belonging to the new workbook, rather than the one in which the code is stored.

Specifying the Container of a Collection

You can reference the containing object of a collection if you prefer to be explicit.  Modify the instructions which count the number of Worksheets, Charts and Sheets to refer to the ThisWorkbook object:

Modify the last three instructions to specify ThisWorkbook as the container for each collection you want to reference.

Clear the contents of the Immediate window and then run the subroutine again:

The result will show the counts of collections in the workbook in which the code is stored, regardless of which workbook is active.

Referring to Embedded Collections

Some collections require an explicit reference to the object which contains the collection.  Create a new subroutine called ReferToEmbeddedCollections in Module1:

Create a subroutine as shown here.

The NFL 2017 worksheet contains two tables, referred to as ListObject objects:

Each table shown here is a ListObject belonging to the ListObjects collection.

Add an instruction to write the Count of the ListObjects collection to the Immediate window:

Refer to the Count property of the ListObjects collection.

Clear the contents of the Immediate window and execute the subroutine.  You'll see an error message as soon as you do so:

The message indicates that it doesn't recognise the name of the ListObjects collection.

You can't refer to the ListObjects collection without first referencing the object which contains it.  In this case, the containing object is the worksheet called NFL 2017.  Add code to reference the correct worksheet before the ListObjects collection:

Here we're using the code name that we've assigned to the worksheet - wsNFL2017.

Try running the subroutine again and check the results in the Immediate window:

This time you should see the number of tables appears as expected.

The same worksheet also contains two embedded charts which belong to the ChartObjects collection.  Add a line of code to display the count of items in this collection:

You must refer to the container of the ChartObjects collection.

Clear the contents of the Immediate window and run the subroutine again:

The results will appear as shown here.

To practise referring to collections:

1. In the same module, create a new subroutine called ChartCollections:

2. Add an instruction to print the Count of the Charts collection of the workbook in which the code is stored:

3. Add an instruction to print the Count of the SeriesCollection collection:

4. Attempt to run the code and confirm that you see an error message:

The SeriesCollection collection will not be recognised.  Click OK to proceed.

5. Edit the second instruction you have written to refer to the AFC Chart as the container of the SeriesCollection.  You can use the code name of this chart - chAFC:

6. Clear the contents of the Immediate window and run the subroutine again:

This is the output you should see.

7. Save and close the workbook.