How to return the address of the range of plotted data on an XY scatter chart?

I can't figure out the VBA code that will return the address of the
range of data plotted on an embedded chart on say sheet1.
Everything I try just results in the "object does not support this
property or method" error message.
I've been recording macros for clues as well as studying the Object
Browser, all to no avail.

Any clues?

Ken Johnson

Hi,

To get the ranges used by a chart data series use the formula property.
Use this in the immediate window.

?activechart.SeriesCollection(1).formula
=SERIES(,Sheet1!$A$1:$A$5,Sheet1!$B$1:$B$5,1)

See John Walkenbach's page on using a classobject to parse the SERIES
formula.
http://j-walk.com/ss/excel/tips/tip83.htm

Cheers
Andy

Ken Johnson wrote:
> I can't figure out the VBA code that will return the address of the
> range of data plotted on an embedded chart on say sheet1.
> Everything I try just results in the "object does not support this
> property or method" error message.
> I've been recording macros for clues as well as studying the Object
> Browser, all to no avail.
>
> Any clues?
>
> Ken Johnson
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

Thank you very much for that information.
I can see now why I was having so much trouble.

Ken Johnson

Hi Andy,

When I type this in the Immediate Window...

sheet1.ChartObjects("Chart 1").activate
?activechart.SeriesCollection(1).formula

the returned value is..

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

which is fine.

I thought I could skip the activate step so I tried...

?sheet1.ChartObjects("Chart 1").SeriesCollection(1).formula

which did not work.

Is it therefore true to say that with embedded charts the code must
activate the chart before the SeriesCollection can be accessed, or am I
doing something wrong?

Ken Johnson

No need to activate, just need the Chart part of the OM.

?sheet1.ChartObjects("Chart 1").chart.SeriesCollection(1).formula

Cheers
Andy

Ken Johnson wrote:
> Hi Andy,
>
> When I type this in the Immediate Window...
>
> sheet1.ChartObjects("Chart 1").activate
> ?activechart.SeriesCollection(1).formula
>
> the returned value is..
>
> =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)
>
> which is fine.
>
> I thought I could skip the activate step so I tried...
>
> ?sheet1.ChartObjects("Chart 1").SeriesCollection(1).formula
>
> which did not work.
>
> Is it therefore true to say that with embedded charts the code must
> activate the chart before the SeriesCollection can be accessed, or am I
> doing something wrong?
>
> Ken Johnson
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks again Andy.

Ken Johnson