chart vs. chartobject

[Andreas Wendzel]

Hi,

I have different problems with the object model of charts in Excel.
Are there any good information about this object model? I only find
small examples.
I want to create an xy-chart with several lines composed of at least
three points.
Is there a way to do this point by point? My first trial makes this by
creating a range, which is made with union for each point.
I also want to add a label to some of this points. Is it possible to
do this while creating the serie? Would be easier for me.
I don't want to be bearish, but I have some other questions as well:
What happens to the chart after this line:
chChart.Location Where:=xlLocationAsObject, Name:="sheet 1"
Any attemp to access "SeriesCollection" raises an error.
Is there a possibility to create a new chart direct in a sheet - not
as an extra sheet?
And the very last question:
I want to move the chart on the sheet. For this, I trie to use the
chartobject.
But this line doesn't work:
Dim coChartObject As ChartObject
Set coChartObject = chChart.Parent

Thanks in advance,

Andreas

 

[Jon Peltier]

As soon as you change the chart from a chart sheet to an embedded chart, the
reference to the chart sheet is lost. I only use Charts.Add to make a new
chart if the chart will be a chart sheet. If I intend to embed the chart in
another sheet, I use Sheets("My Sheet").ChartObjects.Add. See here for
clues:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#addchart

- Jon

 

[Andreas Wendzel]

Hi Jon,

thanks for your answer. It helps me a lot.
How can I determine which cell the source of a point in x-y diagramm
is?
I tried to use srSeries.Points() but I can't find any order in this
collection.
Thanks,

Andreas

 

[Jon Peltier]

You have to parse the series formula to discover the range containing the
data, then knowing it's the Kth point of the series, identify the Kth cell
of the range. I did this on a project a couple years back, so I could find
the routine, but it would take a while to find the backup CD. Use John
Walkenbach's chart series formula module to parse the formula:

http://www.j-walk.com/ss/excel/tips/tip83.htm

- Jon