Page 361 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

For each series in the chart, the XValues and Name properties are set equal to themselves.
Although these properties can be assigned range references, they always return an array of
values when they are referenced. This behavior can be used to convert the cell references
to arrays.

Keep in mind that the number of data points that can be contained in an array reference is
limited to approximately 250 characters. The code will fail if the limits are exceeded, so use an
error trap to cover this possibility.

Defining a Chart’s Labels
Adding data labels to a chart is easy, as long as the labels are based on the data series values or
x-axis values. These options are available using the Chart menu and Chart Options.
You can also enter your own text or formula into each label, but this involves a lot of manual
work. You would need to add standard labels to the series and then individually select each
one and either replace it with your own text or click in the formula bar and enter a formula.
To save time and effort, you can write a macro to achieve the same results.
Figure 15-4 displays the Garden Product Sales Chart with the Monthly Sales and the top sell­ Chapter 15
ing product. The labels have been defined by formulas linked to row 4 of the worksheet, and
as you can see, Fertilizer was the top-selling product in April. The formula in the formula bar
points to cell E4.

Figure 15-4. The labels have been programmed with formulas to point to a cell in
the worksheet.

335