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

[Ken Johnson]

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]

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]

Hi Andy,

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

Ken Johnson

 

[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

 

[Andy Pope]

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

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

Cheers
Andy

 

[Ken Johnson]

Thanks again Andy.

Ken Johnson