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

  • Thread starter Thread starter Ken Johnson
  • Start date Start date
K

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
 
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
 
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
 
Back
Top