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.

Search JabSto ::

Custom Search