Collections and Loops
Exercise 6.01
Exercise 6.01
The aim of this exercise is to alter the formatting of multiple chart objects in a worksheet by looping over the appropriate collection.
Files Needed
Click here to download the file needed for this exercise.
Instructions
- Extract and open the Formatting Charts.xlsm workbook:

The workbook contains a single worksheet with a collection of twelve ChartObjects, some of which are shown here.
- Open the VBE, insert a new module and create a subroutine called Different_Chart_Styles.
- Declare an Integer variable in the subroutine.
- Use the variable to create a For Next loop which counts from 1 to the count of the ChartObjects collection on Sheet1.
As the worksheet contains twelve chart objects you could simply construct the loop to count from 1 to 12.
- Within the loop, add an instruction to change the ChartStyle property using the value of the integer variable. Use the example below as a guide:
Sheet1.ChartObjects(i).Chart.ChartStyle = i
- Run the subroutine and check the results in Excel:

Each chart will have a different format.
- Create a new subroutine called Same_Chart_Style.
- Declare a variable which can hold a reference to a ChartObject object.
- Use this variable to write a For Each loop which processes the ChartObjects collection on Sheet1.
- Within the loop, add an instruction which changes the ChartStyle property of the ChartObject referenced by the variable. Set the chart style to any number between 1 and 12:
co.Chart.ChartStyle = 4
- Run the subroutine and check the results in Excel:

This time, each chart will have the same formatting.
- Save and close the workbook.
Answer Files
Click here to download a file containing a suggested answer.
This page has 0 threads
Add post