Microsoft Office Tutorials and References

In Depth Information

**Determining the Ranges Used in a Chart**

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(,,Sheet1!$B$2:$B$4,1)

=SERIES(,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.