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

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

2. Open the VBE, insert a new module and create a subroutine called Different_Chart_Styles.
3. Declare an Integer variable in the subroutine.
4. Use the variable to create a For Next loop which counts from 1 to the count of the ChartObjects collection on Sheet1.

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

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

Each chart will have a different format.

7. Create a new subroutine called Same_Chart_Style.
8. Declare a variable which can hold a reference to a ChartObject object.
9. Use this variable to write a For Each loop which processes the ChartObjects collection on Sheet1.
10. 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:

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

This time, each chart will have the same formatting.

12. Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.