chart.add gives compile error

[Guest]

I'm trying to automate chart plotting from data in range(B5:C15), using the
following code:

Dim Chrt As Chart

Set Chrt = charts.Add
With Chrt
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Data").Range("b5:c15"), PlotBy:=xlRows
.HasTitle = True
End With

Running this generates an error message at the charts.add command (Compile
Error: Method or Data Member not Found). If I record a macro using the
chart wizard, then "charts.add" is included in the recorded code (and the
chart is created fine), but if I try to run the recorded macro again, the
same error message occurs.

I can get round this using the "chartobjects.add" routine

With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("data").Range("b5:c15")
.Chart.ChartType = xlXYScatterLines
End With

from http://www.peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
but I'm baffled as to why charts.add isn't working - anyone have any ideas?

 

[Rob Bovey]

Your code works fine for me. Note that it will add a separate chart
sheet rather than a chart object on a worksheet. I notice that the "Charts"
method is not capitalized in your code. This is often a sign that you have a
variable, class, or procedure named "charts" and VBA is getting confused
between the two.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Guest]

Ah, stupid me - I'd named the module 'Charts' - and like you say, VBA was
getting confused. Changed the module name, and everything works fine. Many
thanks for your help, Rob!