Microsoft Office Tutorials and References
In Depth Information
Counting and Looping through Charts
Counting and Looping
The examples in this section deal with various aspects of counting charts and
looping through all charts in a worksheet or workbook.
Counting Chart sheets
To determine how many Chart sheets are in the active workbook, access the Count
property of the Charts collection. For example, the following statement displays
the number of Chart sheets in the active workbook:
Counting embedded charts
To count the number of embedded charts on a particular worksheet, access the Count
property of the ChartObjects collection. The following statement, for example,
displays the number of embedded charts on Sheet1 of the active workbook.
Looping through all charts
A common task is to perform an operation on all existing charts. For example, you
may want to write a macro to resize all embedded charts, or add the current date to
each chart’s title. The following macro loops through all embedded charts on the
active worksheet and displays a list of their names.
Dim ChtObj As ChartObject
Dim Msg As String
Msg = “”
For Each ChtObj In ActiveSheet.ChartObjects
Msg = Msg & ChtObj.Name & vbNewLine
The LoopThruChartObjects macro uses a variable named Msg to store the names
of the charts and then displays the list in a message box. vbNewLine is a built-in
constant that adds a line break to the Msg variable.
The listing that follows is a modified version of the macro that uses two loops:
One loop cycles through all worksheets, another loop cycles through all embedded