Using VBA to determine the ranges used in a chart

You might need a VBA macro that must determine the ranges used by each series in chart. For example, you might want to increase the size of each series by adding a new cell. Following is a description of three properties that seem relevant to this task:

♦ Formula property: Returns or sets the SERIES formula for the Series. When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.

♦ Values property: Returns or sets a collection of all the values in the series. This can be a range on a worksheet or an array of constant values but not a combination of both.

♦ XValues property: Returns or sets an array of X values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values, but it can't be a combination of both. The Xvalues property can also be empty.

If you create a VBA macro that needs to determine the data range used by a particular chart series, you might think that the Values property of the Series object is just the ticket. Similarly, the XValues property seems to be the way to get the range that contains the X values (or category labels). In theory, that certainly seems correct. But in practice, it doesn't work. When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, it is always an array. Unfortunately, the object model provides no way to get a Range object used by a Series object.

One possible solution is to write code to parse the SERIES formula and extract the range addresses. This sounds simple, but it's actually a difficult task because a SERIES formula can be very complex. Following are a few examples of valid SERIES formulas.

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

=SERIES("Sales Summary",,Sheet1!$B$2:$B$4,1)

=SERIES(,{"Jan","Feb","Mar"},Sheet1!$B$2:$B$4,1)

=SERIES(,(Sheet1!$A$2,Sheet1!$A$4),(Sheet1!$B$2,Sheet1!$B$4),1)

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1,Sheet1!$C$2:$C$4)

As you can see, a SERIES formula can have missing arguments, use arrays, and even use noncontiguous range addresses. And to confuse the issue even more, a bubble chart has an additional argument (for example, the last SERIES formula in the preceding list). Attempting to parse out the arguments is certainly not a trivial programming task

I worked on this problem for several years, and I eventually arrived at a solution. The trick involves evaluating the SERIES formula by using a dummy function. This function accepts the arguments in a SERIES formula and returns a 2 x 5 element array that contains all the information in the SERIES formula.

I simplified the solution by creating four custom VBA functions, each of which accepts one argument (a reference to a Series object) and returns a two-element array. These functions are the following:

♦ SERIESNAME_FROM_SERIES: The first array element contains a string that describes the data type of the first SERIES argument (Range, Empty, or String). The second array element contains a range address, an empty string, or a string.

♦ XVALUES_FROM_SERIES: The first array element contains a string that describes the data type of the second SERIES argument (Range, Array, Empty, or String). The second array element contains a range address, an array, an empty string, or a string.

♦ VALUES_FROM_SERIES: The first array element contains a string that describes the data type of the third SERIES argument (Range or Array). The second array element contains a range address or an array.

♦ BUBBLESIZE_FROM_SERIES: The first array element contains a string that describes the data type of the fifth SERIES argument (Range, Array, or Empty). The second array element contains a range address, an array, or an empty string. This function is relevant only for bubble charts.

Note that there is not a function to get the fourth SERIES argument (plot order). This argument can be obtained directly by using the PlotOrder property of the Series object.

The VBA code for these functions is too lengthy to be listed here, but it's available on the companion CD-ROM. It's documented in such a way that it can be easily adapted to other situations.

The following example demonstrates: It displays the address of the values range for the first series in the active chart.

Sub ShowValueRange() Dim Ser As Series Dim x As Variant

Set Ser = ActiveChart.SeriesCollection(1) x = VALUES_FROM_SERIES(Ser) If x(1) = "Range" Then

MsgBox Range(x(2)).Address End If End Sub

The variable x is defined as a variant and will hold the two-element array that's returned by the VALUES_FROM_SERIES function. The first element of the x array contains a string that describes the data type. It the string is Range, the message box displays the address of the range contained in the second element of the x array.

Figure 18-6 shows another example. The chart has three data series. Buttons on the sheet execute macros that expand and contract each of the data ranges.

The ContractAllSeries procedure is listed below. This procedure loops through the SeriesCollection collection and uses the XVALUE_FROM_SERIES and the VALUES_FROM_SERIES functions to retrieve the current ranges. It then uses the Resize method to decrease the size of the ranges.

Sub ContractAllSeries() Dim s As Series Dim Result As Variant

Dim DRange As Range For Each s In ActiveSheet.ChartObjects(1).Chart.SeriesCollection Result = XVALUES_FROM_SERIES(s) If Result(1) = "Range" Then

Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then

Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.XValues = DRange End If End If

Result = VALUES_FROM_SERIES(s) If Result(1) = "Range" Then

Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then

Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.Values = DRange End If End If Next s End Sub

The ExpandAllSeries procedure is very similar. When executed, it expands each range by one cell.

Figure 18-6: This workbook demonstrates how to expand and contract the chart series by using VBA macros.
+1 0

Responses

  • Bailey Aitken
    How to expand chart data selection using vba?
    7 years ago

Post a comment