Microsoft Office Tutorials and References

In Depth Information

**Changing the Data Used in a Chart**

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, an array is always returned. 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 task sounds simple, but it’s actually difficult 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 the

arguments is certainly not a trivial programming task.

I spent a lot of time working on this problem, and I eventually arrived at a solution that involves

evaluating the SERIES formula by using a dummy function. This function accepts the same

arguments as 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:

h
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.

h
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.

h
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.

h
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.