Excel Charts and VBA

[Charles]

Hello

I must be a complete Idiot but I never managed to get VBA to work with
Excel Charts. Would anyone be kind enought to show me what syntax
works with charts? I must say the "Record Macro" function doesn't
really help to generate clean codes.

Let's say I have several charts on a sheet. I would like to add a new
series to an existing XY chart (chart #2 and I don't know how many
series there are already in this chart).

So my code would start with:

Dim S as worksheet
Set S=Thisworkbook.Sheets("NameoftheSheet")
Dim Rg1 as range, Rg2 as range, Rg3 as range
Set Rg1=S.Range("TheXdata")
Set Rg2=S.Range("TheYdata")
Set Rg3=S.Range("TheChartTitle")


Would anyone be able to indicate me how to do it properly?
The "record macro" function only generates relative references to the
data. Is this the only solution (I have range names)? Does it have to
be a string or can it be a range, etc.

I thank you in advance
Charles

 

[Jon Peltier]

The examples on this page should get you started:

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

- Jon

 

[Charles]

Thanks but it doesn't seem to really work. For instance if I do (S
being a worksheet):

Dim TheChart As ChartObject
Set TheChart = S.ChartObjects(1)
TheChart.Chart.Select

excel will invariably throw a "Method "Select" of object "_Chart"
failed. This is the case for any method included in the "Chart"
object. However, the method directly accessible from "TheChart" work
(ex: TheChart.Select)

If I manage to get passed this error, I think I can help myself. But I
really don't know how to get around it. Would someone be able to tell
me what I am doing wrong?

thanks
Charles

 

[Charles]

and for instance this code will result in a similar error:
Dim Ch As Excel.Chart
Set Ch = S.ChartObjects(1).Chart
Ch.Select

 

[Jon Peltier]

1. You can't select a chart. You can select a chart object, and you can
select the chart area or other chart element.

2. You should be trying to make your code work without selecting the chart.
The page I cited discusses this.

- Jon

 

[Charles]

I see. Actually the two functions I was using to test were Select and
Title. Title doesn't work unless hastitle has been set on true, and
select doesn't work at all. This explains that.

thanks!
Charles