Chart Sheets

In Chapter 5, you learned that a collection of Worksheet objects were members of the Worksheets collection object. Chart sheets (see Figure 9.3), on the other hand, are not included with this collection. This makes sense because a chart sheet is not a spreadsheet and should not be contained in a collection object called Worksheets. Instead, chart sheets are members of two different collection objects: the Sheets and Charts collection objects. The Sheets collection object has broader scope, including both Worksheet objects and Chart objects (as chart sheets). This is somewhat unusual because chart sheets and worksheets are really two different beasts, and collection objects generally hold objects of only one type. As you might expect, however, VBA does provide a collection object that contains only chart sheets—the

Charts collection object. As an example, consider a workbook that contains multiple worksheets and chart sheets. All Chart objects can be returned to your program in a Charts collection via the Charts property of the Workbook object.

A chart sheet.

A chart sheet.

Powerful Engram Chart

ActiveWorkbook.Charts

It is important to point out that the Charts collection object returned by the Charts property returns only the chart sheets in the specified workbook. To access an individual chart sheet, specify an index (or object name as a string) with the Charts property.

ActiveWorkbook.Charts(1) or

ActiveWorkbook.ChartsCMyClwt")

Consider the GetChartSheets() sub procedure. This procedure uses a For/Each loop to iterate through a Sheets collection in an attempt to return only those sheets from the active workbook that are chart sheets. This procedure will execute successfully if the active workbook only contains chart sheets—something that you will probably never create. The problem with the GetChartSheets() sub procedure is that any worksheets contained in the active workbook will also be returned in the Sheets collection; therefore, a runtime error is generated (type mismatch) when the current iteration of the loop tries to access a Worksheet object with the variable that was declared as a Chart object (chSheet).

Public Sub GetChartSheets() Dim chSheet As Chart

For Each chSheet In ActiveWorkbook.Sheets Debug.Print chSheet.Name

Next End Sub

To fix the GetChartSheets() sub procedure use the Charts property of the Workbook object to return all Chart objects (as chart sheets) from the active workbook.

Public Sub GetChartSheets() Dim chSheet As Chart

For Each chSheet In ActiveWorkbook.Charts Debug.Print chSheet.Name

Next End Sub

It may seem confusing to use the Charts property to return a collection of chart sheets, and not all charts (including embedded charts) from the workbook. An embedded chart is a chart that has been placed on a worksheet (see Figure 9.4), or a chart sheet (see Figure 9.5). When you think about it, embedded charts are subordinate to a Worksheet object or Chart object (when it references a chart sheet); so it makes sense that you cannot access embedded charts from a property of the Workbook object.

An embedded chart placed on a worksheet.

An embedded chart placed on a worksheet.

Vba Multiplication Table Programming

An embedded chart placed on a chart sheet.

An embedded chart placed on a chart sheet.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

  • samwise
    What is chart placed in a worksheet called?
    2 years ago

Post a comment