Chart Hunting

As you know, a chart can take up residence in two locations: as a chart sheet in the workbook, or as an embedded chart on a worksheet. Setting a reference to an existing chart varies according to what kind of location the chart lives in. For chart sheets, you can refer to them almost like a worksheet as the following code snippet demonstrates.

Dim chrtl As Chart Dim chrt2 As Chart

' set a reference to the chart sheet named Chart4 Set chrtl = ThisWorkbook.Charts("Chart4")

' set a reference to the 2nd chart sheet in the wb Set chrt2 = ThisWorkbook.Charts(2)

If your chart is embedded in a worksheet, however, you need to go through the ChartObjects collection, which is a property of the Worksheet object that contains the embedded chart. An example of this method is shown in the following snippet.

Dim chrtl As Chart Dim chrt2 As Chart

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(l)

' set a reference to the embedded chart named Chart4 Set chrtl = ws.ChartObjects("Chart4").Chart

' set a reference to the 2nd embedded chart Set chrt2 = ws.ChartObjects(2).Chart

Notice that to get to the actual chart object you need to locate a specific ChartObject first. You can think of a ChartObject as a special object that exists merely to contain a chart on a worksheet. Much like a flea needs a dog, a Chart needs a ChartObject to reside on a worksheet.

You can apply all of the techniques you learned about looking for specific worksheets in a workbook to looking for chart sheets. Looking for embedded charts is another story due to two minor complications. First, it isn't practical to know the index order of the chart objects on a worksheet. If you only have one chart on a worksheet, you're fine (but who's to say that another chart doesn't get added unbeknownst to you?). Second, unless you named the chart when you created it, you can't refer to it by name.

What to do? Simple, create a procedure that returns a chart based on some identifying characteristic. For example, you could retrieve charts according to the chart title, one of the axis titles, the source data range, or any of the other identifying characteristics. Listing 10.11 presents an example of a procedure that searches for and returns a chart based on a chart title.

0 0

Post a comment