Module 6 - Collections and Loops
Lesson 6.1 - Collections and Objects
Topic 6.1.2 - Object references by name

In the previous part of the lesson we covered how to refer to collections but it's also quite important to be able to reference the individual objects in a collection!  This part of the lesson explains how to reference objects by name.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

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:

Worksheets

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:

basic sub

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:

Refer to 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:

Name object

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:

Select worksheet

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 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:

Move chart

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 move

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:

New subroutine

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: 

Table to reference

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

 

Now select the Design tab in the Excel ribbon:

Design tab

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:

Table name

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:

Reference collection

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 table

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:

Alter style

Change the TableStyle property as shown here.

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

Table styles

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:

Format table

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 variable

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

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

 

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

Use 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:

Result

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:

Sub ReferToChart()

 

Charts("AFC Chart").Select

 

End Sub

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

Sub ReferToChart()

 

Charts("AFC Chart").Select

Charts("AFC Chart").ChartType = xlColumnStacked

 

End Sub

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

Sub ReferToChart()

 

Charts("AFC Chart").Select

Charts("AFC Chart").ChartType = xlColumnStacked

 

Charts("NFC Chart").Select

Charts("NFC Chart").ChartType = xlColumnStacked

 

End Sub

  1. Run the subroutine and check the results in Excel:
NFC chart

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:

Sub ReferToEmbeddedChart()

 

Worksheets("AFC 2017").Select

 

End Sub

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

Sub ReferToEmbeddedChart()

 

Dim co As ChartObject

 

Worksheets("AFC 2017").Select

 

End Sub

  1. 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:
Chart name

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

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

Sub ReferToEmbeddedChart()

 

Dim co As ChartObject

 

Worksheets("AFC 2017").Select

 

Set co = ActiveSheet.ChartObjects("Chart 1")

 

End Sub

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

Sub ReferToEmbeddedChart()

 

Dim co As ChartObject

 

Worksheets("AFC 2017").Select

 

Set co = ActiveSheet.ChartObjects("Chart 1")

 

co.Chart.ChartType = xlColumnStacked

co.Chart.ChartStyle = 42

 

End Sub

  1. Run the subroutine and check that the chart has changed:
Chart changed

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

  1. Save and close the workbook.
This page has 0 threads Add post