Excel Chart SeriesCollection Formula

  • Thread starter Thread starter sgpl
  • Start date Start date
S

sgpl

I am trying to use VBA code to capture the X and Y values of existing chart.
The auto convertion to exponential format is give me problem. Please find the
details below:
VBA code :
Set obj = ActiveSheet.ChartObjects(cnt1).Chart
for series_cnt=1 to obj.SeriesCollection.Count
seriesFormula = obj.SeriesCollection(series_cnt).Formula
msgbox seriesFormula
next

The formula which I am trying to retrieve is
SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522,1007654,997132,965041,1006511,965152,976880},2)

From 1006511,965152,976880, it is getting displayed in Exponential format
with output being

SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522,1007654,997132,965041,1E,}

I need vba code which will help us to retain the value as it is and not to
get converted in Exponential format and with no truncation. These are chart
plot points.
 
When I run your code I get the correct formula in my message box.

If you want to get the X and Y values, use this:

vXValues = obj.SeriesCollection(series_cnt).XValues
vYValues = obj.SeriesCollection(series_cnt).Values

vXValues and vYValues should be declared as type Variant, not Variant().
Each contains an array of values.

- Jon
 
Back
Top