Module 6 - Collections and Loops
Lesson 6.1 - Collections and Objects
Topic 6.1.3 - Object references by number

In the previous part of the lesson you saw how to refer to an object in a collection using the object's name.  When you don't know the name of an object, you can instead refer to it using its index number, as this part of the lesson shows you.

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

Sheets

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:

Existing sub

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:

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:

Worksheet selected

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:

Third sheet

Change the reference to the Worksheets collection to Sheets.

 

Run the code again and check the result in Excel:

Third sheet

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:

Second chart

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

 

Run the subroutine and check the results in Excel:

Second chart

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

Excel VBA collections are indexed starting from 1.  Some other VBA libraries index their collections starting from 0.

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:

Basic sub

Create a basic subroutine as shown here.

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

Move

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:

Before Sheet 1

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

 

Run the subroutine and check the result in Excel:

Moved charts

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 code

Alter the code so that it looks like this.

 

Run the subroutine and check the results in Excel:

Moved charts

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:

Basic sub

Create a subroutine as shown here.

 

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

Second table

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:

Second list

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:

Ater style

Change the style of the table as shown here.

 

Run the subroutine and check the results in Excel:

Result

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:

Sub MoveFirstSheetToLast()

 

ThisWorkbook.Activate

 

End Sub

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

Sub MoveFirstSheetToLast()

 

ThisWorkbook.Activate

 

Sheets(1)

 

End Sub

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

Sub MoveFirstSheetToLast()

 

ThisWorkbook.Activate

 

Sheets(1).Move After:=Sheets(Sheets.Count)

 

End Sub

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

Sub MoveLastSheetToFirst()

 

ThisWorkbook.Activate

 

End Sub

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

Sub MoveLastSheetToFirst()

 

ThisWorkbook.Activate

 

Sheets(Sheets.Count).Move Before:=Sheets(1)

 

End Sub

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

Sub FormatSingleDataPoint()

 

Worksheets("NFL 2017").Select

 

End Sub

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

Sub FormatSingleDataPoint()

 

Dim co As ChartObject

 

Worksheets("NFL 2017").Select

 

Set co = ActiveSheet.ChartObjects(1)

 

End Sub

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

Sub FormatSingleDataPoint()

 

Dim co As ChartObject

Dim s As Series

 

Worksheets("NFL 2017").Select

 

Set co = ActiveSheet.ChartObjects(1)

 

Set s = co.Chart.SeriesCollection(2)

 

End Sub

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

Sub FormatSingleDataPoint()

 

Dim co As ChartObject

Dim s As Series

Dim p As Point

 

Worksheets("NFL 2017").Select

 

Set co = ActiveSheet.ChartObjects(1)

 

Set s = co.Chart.SeriesCollection(2)

 

Set p = s.Points(8)

 

End Sub

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

Sub FormatSingleDataPoint()

 

Dim co As ChartObject

Dim s As Series

Dim p As Point

 

Worksheets("NFL 2017").Select

 

Set co = ActiveSheet.ChartObjects(1)

 

Set s = co.Chart.SeriesCollection(2)

 

Set p = s.Points(8)

 

p.Format.Fill.ForeColor.RGB = rgbLime

 

End Sub

  1. Run the subroutine and check the results in Excel:
Result

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

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