Embedded Charts

To access embedded charts, use the ChartObjects collection and ChartObject objects. A ChartObjects collection object contains all ChartObject objects on a worksheet or chart sheet. A ChartObject object is a container for a single Chart object, but not if this Chart object represents a chart sheet. If there was such a thing as a Sheet object, then I would tell you the ChartObjects and ChartObject objects are subordinate objects of the Sheet object; however, there is no Sheet object in the Excel object model. So where do these objects fall in the hierarchy? As I said before, it's confusing at first, but makes sense when you think about it—but the ChartObjects and ChartObject objects are subordinate to the Worksheet object and the Chart object. They are only subordinate, however, to the Chart object when the Chart object represents a chart sheet. Confusion between the ChartObject object and the Chart object will be a common source of error in your VBA code when programming charts. The following example helps clarify how to use these objects to access an embedded chart.

Public Sub GetEmbeddedChartObjects()

Dim chObj As ChartObject

For Each chObj In ActiveSheet.ChartObjects Debug.Print chObj.Chart.Name

Next End Sub

The GetEmbeddedChartObjects() sub procedure loops through all ChartObject objects on the active sheet (chart sheet or worksheet) using a For/Each loop. A Chart object is returned via the Chart property of the ChartObject object and the value of the Chart object's Name property is output to the Immediate window. Please note that to access the actual Chart object and not just the container object, the Chart property of the ChartObject object must be used (chObj.Chart). Without the Chart property (for example, chObj.Name), the preceding procedure would output the value of the Name property of a ChartObject object, which is not the same as the Name property of the Chart object. The point of this is to illustrate that the path to a Chart object contained in an embedded chart is:

Application

Workbook

Worksheet or Chart (as a chart sheet) ChartObject Chart.

You now know how to access Chart objects associated with chart sheets and embedded charts using the VBA objects summarized in Table 9.1. Next, I will discuss some of the methods and properties you can use to manipulate these charts.

Table 9.1 VBA Objects Used to Access Excel Charts r

Object

Sheets collection Charts collection

Chart

Function

A collection of all sheets in the specified workbook, including chart sheets as Chart objects and Worksheet objects.

A collection of all chart sheets in the specified workbook as Chart objects.

Represents a single Chart object (embedded or as a chart sheet).

ChartObjects collection A collection of all ChartObject objects on a specified worksheet or chart sheet.

ChartObject

Represents the container object for an embedded Chart object.

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


Post a comment