The Data SeriesVBA Name Series

Each series in a chart is a member of the SeriesCollection collection. The sample chart has two data series (Xdata and Ydata). The VBA syntax for specifying a given series is

Cht.SeriesCollection(Index)

Index can be a number ranging from 1 to the number of series in the chart or the name of the series. When you click any data point (column) belonging to Xdata on the chart, you see the following formula in the formula bar:

=SERIES(Sheet1!$A$1,,Sheet1!$A$2:$A$5,1) Sheet1!$A$1 represents the name of the data series (Xdata).

By default, the second argument is null because the x-axis of a column chart is ordinal in nature: It ranges from 1 to the number of data points. However, this argument can contain a range reference if you intend to show custom x-axis labels.

Sheet1!$A$2:$A$5 is the data range for the series.

Lastly, the 1 represents the index number of the series. In the user interface, you can change the index number of the series with Format Data Series, Series Order.

To specify series Xdata you can use either Charts("Chart1").SeriesCollection("Xdata")

Charts("Chart1").SeriesCollection(1)

Here is a macro to create a combination chart with series Xdata as a line series. The code needs to change the Xdata series to Line type, set the line weight to the heaviest value, and place black circular markers of size 10 points.

Sub SeriesDemo()

Dim Ser As Series

Set Ser = Charts("Chart1").SeriesCollection("XData") With Ser

.ChartType = xlLine .Border.Weight = xlThick .MarkerStyle = xlMarkerStyleCircle .MarkerBackgroundColorlndex = xlAutomatic .MarkerForegroundColorlndex = xlAutomatic .MarkerSize = 10 End With End Sub

Running this macro modifies the chart as shown in Figure 10.11.

Figure 10.11

Setting a different ChartType for a single series allows you to make interesting combination charts.

Figure 10.11

Setting a different ChartType for a single series allows you to make interesting combination charts.

0 0

Responses

Post a comment