Page 359 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

A SERIES formula uses the following syntax:
=SERIES(name,category_labels,values,order)

● name (Optional) The name used in the legend. If the chart has only one series, the
name argument is used as the title.
● category_labels (Optional) The range that contains the labels for the category axis.
If omitted, Excel uses consecutive integers beginning with 1.
● values The range that contains the values.
● order An integer that specifies the plotting order of the series (relevant only if the
chart has more than one series).
Range references in a SERIES formula are always absolute, and they always include the
sheet name, as in the following example:

=SERIES(,Sheet1!$B$1,Sheet1!$B$2:$B$7,1)
A range reference can consist of a noncontiguous range. If so, each range is separated by
a comma, and the argument is enclosed in parentheses. In the following SERIES formula, Chapter 15
the values’ ranges consist of B2:B3 and B5:B7:

=SERIES(,,(Sheet1!$B$2,Sheet1!$B$5:$B$7),1)
You can substitute range names for the range references. If you do so, Excel changes the
reference in the SERIES formula to include the workbook, as in the following example:
=SERIES(Sheet1$B$1,,budget.xls!MyData,1)

Modifying a Chart to Use Data from Arrays
A chart series can be defined by assigning a VBA array to its Values property. This is useful if
you need to generate a chart that is not linked to the original data. The chart can be distrib­
uted in a separate workbook that’s independent of the source data.
Figure 15-3 displays the Garden Company Product Sales Chart with the Catalog series
selected. Notice the difference between this graphic and the graphic located within the
Inside Out: “How the Chart SERIES Formula Works” that displayed the Catalog series using
information from Sheet1. You can see the definition of the first data series in the formula bar
above the worksheet. The values on the y-axis are defined by an Excel array. The category
names have been assigned as text to the series names.

333