The Example Workbook

Download and extract the workbook linked to in the Files Needed section above.  You'll find a copy of the workbook that we used in the previous part of this lesson.

Referring to an Object in a Collection by Name

Usually, the most convenient way to reference a specific object in a collection is to use its name.  The example workbook contains a collection of three worksheets, each of which has a unique name:

The first three tabs shown here are worksheet objects belonging to the Worksheets collection. Each object has a unique name, as shown on the tab.

Open the VBE and find the subroutine called ReferToObjectByName in Module1:

The subroutine contains a single instruction to ensure that the workbook which contains the code is activated.

If we wanted to write an instruction which selected the AFC 2017 worksheet, we can start by writing the name of the Worksheets collection:

Start by referring to the collection to which the object belongs and open a set of parentheses.

You can then write the name of the object within a set of parentheses and double-quotes after the collection name:

Enter the object name within a set of double-quotes then close the parentheses.

After the parentheses you can enter a full stop followed by the method or property of the object that you want to use:

Here we've applied the Select method to the worksheet.

You can reference most objects which belong to a collection in this way.  Add a new instruction which begins by referring to the chart called AFC Chart:

Refer to the Charts collection followed by the name of the chart in parentheses and double-quotes.

Apply the Move method to the chart to place it after the AFC 2017 worksheet:

Use the After parameter of the Move method to specify where to put the chart. Refer to the AFC 2017 worksheet as shown here.

Run the subroutine and check the results in Excel:

After moving the chart you should find that it has been selected automatically.

Referring to Embedded Objects by Name

You can refer to objects in embedded collections in a similar way.  You must ensure that you refer to the container object before the name of the collection.  To demonstrate this, we'll refer to one of the table objects on the AFC 2017 worksheet.  Start by creating a subroutine called ReferToEmbeddedObject and add an instruction to select the AFC 2017 worksheet:

Create a basic subroutine as shown here.

In order to reference the table you first need to know what its name is.  Start by selecting the table you want to reference: 

Select any cell in the first table on the AFC 2017 worksheet.

Now select the Design tab in the Excel ribbon:

This tab appears when you select a cell in the table.

You can find the name of the selected table on the left of the Design tab:

The name of the table appears in this box. You can rename the table by typing a new name into the box but we'll stick with the default name for now.

Now that we know the name of the table we can reference it.  In order to do this, we need to refer to the worksheet which contains the ListObjects collection.  Add an instruction to the subroutine which references the ListObjects collection of the active worksheet:

As we've selected the worksheet which contains the table, we can refer to the ActiveSheet object.

You can then enter the name of the table in a set of parentheses and double quotes:

Reference the table using the name you discovered earlier.

You can now access the properties and methods of the table object.  In the example below we're using the TableStyle property to alter the formatting of the table:

Change the TableStyle property as shown here.

You can find other table styles in the Design tab of the ribbon:

You can hover the mouse cursor over a style to see its name appear in the tooltip.

You'll need to modify the name shown in the tooltip to remove the spaces when you write it in VBA.  The style shown above is Table Style Light 21 in the tooltip but needs to be written as TableStyleLight21 in the VBA code.

Execute the subroutine to check that it works:

The table should be formatted according to the style you have applied to it.

Using Object Variables

Referencing embedded objects can be awkward as you must begin the instruction by referring to the container object.  You can use object variables to make the code easier to write.  Edit the ReferToEmbeddedObject subroutine to declare a variable which can hold a reference to a ListObject object:

Declare a variable as shown here.

Alter the instruction which changes the table style to set a reference to Table3 in the variable you have declared:

Set the variable to refer to the object in the ListObjects collection.

Add an instruction to alter the TableStyle property using the variable:

You can use the variable to access the methods and properties of the object to which it refers.

Run the subroutine to check that it works:

Feel free to experiment with less ugly table styles!

Referring to Objects by Name

To practise referring to objects by name:

1. In Module1 create a new subroutine called ReferToChart and add an instruction which selects the chart called AFC Chart:

2. Add an instruction which changes the ChartType property of the chart to xlColumnStacked:

3. Add two more instructions to select and change the chart type of the chart called NFC Chart:

4. Run the subroutine and check the results in Excel:

The charts should resemble the example shown here.

Referring to Embedded Objects

To practise referring to embedded objects:

1. Create a new subroutine called ReferToEmbeddedChart in Module1 and add an instruction to select the AFC 2017 worksheet:

2. Declare a variable which can hold a reference to a ChartObject object:

3. In Excel, select the first chart on the AFC 2017 worksheet and find its name in the Name Box at the top left of the worksheet:

Select the chart object and look at the top left of the worksheet. The Name Box shows that this chart is called Chart 1.

4. Add an instruction to set a reference to this chart in the variable you have declared:

5. Add instructions to change the chart type and chart style of the Chart within the ChartObject container:

6. Run the subroutine and check that the chart has changed:

You may wish to experiment with the number assigned to the ChartStyle property!

7. Save and close the workbook.