The Example Workbook

Download and extract the workbook linked to in the Files Needed section above.  You'll find a copy of the workbook you used in the previous part of the lesson containing a number of worksheets, charts and tables.

Referring to an Object by Index Number

The sample workbook you have opened contains three objects in the Worksheets collection and two objects in the Charts collection:

Altogether, there are five objects in the Sheets collection.

You can reference an object in any of these three collections using the index number of the object.  Objects in these collections are numbered based on their position from left to right.  We can refer to the NFC 2017 worksheet as worksheet number 3.  Open the VBE and find the subroutine called ReferToObjectByNumber:

The subroutine contains a single instruction to activate the workbook that contains the code.

Add an instruction to the subroutine which selects the third worksheet:

Refer to the Worksheets collection and enter the number of the object in parentheses. You can then enter a full stop and apply the Select method.

Run the subroutine and check the result in Excel:

You should find that the NFC 2017 worksheet has been selected.

Edit the code to apply the Select method to the third object in the Sheets collection:

Change the reference to the Worksheets collection to Sheets.

Run the code again and check the result in Excel:

This time the AFC Chart will be selected as it is the third item in the Sheets collection.

Alter the code to select the second object in the Charts collection:

Change the Sheets collection to the Charts collection and refer to item number 2.

Run the subroutine and check the results in Excel:

The second item in the Charts collection is also the fifth item in the Sheets collection.

Arranging Sheets Based on Position

Using the index number of a sheet is useful when you want to rearrange the order of sheets and you can't rely on the sheet names.  Add a new subroutine called ArrangeSheets and add an instruction to activate ThisWorkbook:

Create a basic subroutine as shown here.

Add an instruction which applies the Move method to the Charts collection:

The Move method allows you to specify which sheet the charts should be placed before or after.

To place the Charts collection to the left of all the existing sheets we can refer to item number 1 in the Sheets collection:

Pass a reference to the first sheet object to the Before parameter.

Run the subroutine and check the result in Excel:

The two charts will appear to the left of all the worksheets.

If we wanted to place the Charts collection to the right of the worksheets we could refer to item number 5 in the Sheets collection.  The problem is that we might add more sheets later.  To solve this, we can use the Count property of the Sheets collection.  Alter the code to place the charts after the sheet whose index number is equal to the count of sheets:

Alter the code so that it looks like this.

Run the subroutine and check the results in Excel:

Now the charts will appear to the right of all the other sheets.

Referring to Embedded Objects

You can refer to objects embedded within another object using the number of the object.  Create a new subroutine called ReferToEmbeddedObject and add an instruction to select the AFC 2017 worksheet:

Create a subroutine as shown here.

We'd like to refer to the second item in the ListObjects collection on the worksheet:

The worksheet contains four ListObject objects and we'd like to refer to the second one.

Add a variable to hold a reference to a ListObject and set a reference to the second item in the ListObjects collection on the worksheet:

Precede the ListObjects collection with a reference to the container object. In this case you can refer to the ActiveSheet.

Add an instruction to modify the TableStyle property of the object referenced by the variable:

Change the style of the table as shown here.

Run the subroutine and check the results in Excel:

The exact appearance of the table will depend on which theme the workbook uses.

Referring to Objects by Index Number

To practise referring to objects by index number:

1. In Module1 create a new subroutine called MoveFirstSheetToLast and add an instruction to activate ThisWorkbook:

2. Add an instruction which refers to the first object in the Sheets collection:

3. Apply the Move method to the object and use the After parameter to reference the last object in the Sheets collection:

4. Create a new subroutine called MoveLastSheetToFirst and add an instruction to activate ThisWorkbook:

5. Add an instruction to move the last object in the Sheets collection before the first object in the Sheets collection:

6. Test that each subroutine works.

Referring to Embedded Objects

To practise referring to embedded objects:

1. Create a new subroutine called FormatSingleDataPoint and add an instruction which selects the NFL 2017 worksheet:

2. Add a variable which can hold a reference to a ChartObject object and use it to store a reference to the first item in the ChartObjects collection of the worksheet:

3. Declare a variable which can hold a reference to a Series object.  Use it to store a reference to the second item in the SeriesCollection collection of the Chart property of the object referenced by the first variable:

4. Declare a variable which can hold a reference to a Point object.  Use it to store a reference to the eighth item in the Points collection of the object referenced by the Series variable:

5. Use the Point variable to change the formatting of the data point:

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

Running the code should highlight the data point for the team which lost every game in the season.

7. Save and close the workbook.