It is easy to create a programmatic reference to a chart on a chart sheet. The Chart object is a member of the Charts collection of the workbook. To reference a chart embedded in a worksheet, you need to be aware that the Chart object is contained in a ChartObject object that belongs to the ChartObjects collection of the worksheet. ChartObject objects also belong to the Shapes collection of the worksheet.

You can move or resize an embedded chart by changing the Top, Left, Width, and Height properties of the ChartObject. If you already have a reference to the Chart object, you can get a reference to the ChartObject object through the Parent property of the Chart object.

Individual series in a chart are Series objects, and they belong to the SeriesCollection object of the chart. The Delete method of the Series object is used to delete a series from a chart. You use the NewSeries method of the SeriesCollection object to add a new series to a chart.

You can assign a VBA array, rather than the more commonly used Range object, to the Values property of a Series object. This creates a chart that is independent of worksheet data and can be distributed without a supporting worksheet.

The Values and XValues properties return data values, not the range references used in a chart. You can determine the ranges referenced by a chart by examining the SERIES function in the Formula property of each series.

The data points in a chart are Point objects and belong to the Points collection of the Series object. Excel does not provide an easy way to specify cell values as labels on series data points through the user interface. However, this can be easily done using VBA code.

0 0

Post a comment