Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the sample code.
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.
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.
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.
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!
To practise referring to objects by name:
Sub ReferToChart()
Charts("AFC Chart").Select
End Sub
Sub ReferToChart()
Charts("AFC Chart").Select
Charts("AFC Chart").ChartType = xlColumnStacked
End Sub
Sub ReferToChart()
Charts("AFC Chart").Select
Charts("AFC Chart").ChartType = xlColumnStacked
Charts("NFC Chart").Select
Charts("NFC Chart").ChartType = xlColumnStacked
End Sub
The charts should resemble the example shown here.
To practise referring to embedded objects:
Sub ReferToEmbeddedChart()
Worksheets("AFC 2017").Select
End Sub
Sub ReferToEmbeddedChart()
Dim co As ChartObject
Worksheets("AFC 2017").Select
End Sub
Select the chart object and look at the top left of the worksheet. The Name Box shows that this chart is called Chart 1.
Sub ReferToEmbeddedChart()
Dim co As ChartObject
Worksheets("AFC 2017").Select
Set co = ActiveSheet.ChartObjects("Chart 1")
End Sub
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
You may wish to experiment with the number assigned to the ChartStyle property!
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.