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
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.
Search JabSto ::

Custom Search