Problem creatting charts with VBA

[TheWizEd]

In Excel 2007, when I create a new chart using Charts.Add I get a chart that
has all the series of a previously created chart. How can I prevent this? I
want a clean slate. I know that in the past I could not avoid having at
least one series when creating a chart. I simply redefine the attributes of
series one. And this seems to be true of 2007 as well.

 

[Mike Middleton]

TheWizEd -

Jon Peltier's web page, "Quick Excel Chart VBA Examples," might be useful,
at

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

Note that he uses ChartObjects.Add instead of Charts.Add to get more
"control."

- Mike
http://www.MikeMiddleton.com


In Excel 2007, when I create a new chart using Charts.Add I get a chart that
has all the series of a previously created chart. How can I prevent this?
I
want a clean slate. I know that in the past I could not avoid having at
least one series when creating a chart. I simply redefine the attributes of
series one. And this seems to be true of 2007 as well.

 

[TheWizEd]

Mike,

I'm not trying to embed a chart but create a chart sheet.

 

[TheWizEd]

In Excel 2007 the Charts.Add seems to produce weird results. It seems as
though it isn't creating a new chart but copying the last chart and appending
series to it. Try this.

Option Explicit

Sub CreateCharts()
Dim i As Integer
Dim r As Range
Dim c As Chart
Dim s As Series

' Populate with some data
Set r = Worksheets("Sheet1").Range("A1")
For i = 0 To 4
r.Offset(i, 0) = i + 1
r.Offset(i, 1) = i * 10
r.Offset(i, 2) = 1.1 * r.Offset(i, 1)
r.Offset(i, 3) = 1.5 * r.Offset(i, 1)
r.Offset(i, 4) = 50 - (i * 10)
r.Offset(i, 5) = 1.1 * r.Offset(i, 4)
r.Offset(i, 6) = 1.5 * r.Offset(i, 4)
Next

Set c = Charts.Add
c.ChartType = xlXYScatterSmooth
c.PlotBy = xlColumns
MsgBox c.SeriesCollection.Count
For i = c.SeriesCollection.Count To 2 Step -1
c.SeriesCollection(i).Delete
Next
Set s = c.SeriesCollection(1)
s.Values = Range(r.Offset(0, 1), r.Offset(4, 1))
s.XValues = Range(r.Offset(0, 0), r.Offset(4, 0))
Set c = Charts.Add
c.ChartType = xlXYScatterSmooth
c.PlotBy = xlColumns
MsgBox c.SeriesCollection.Count
Set s = c.SeriesCollection(1)
s.Values = Range(r.Offset(0, 2), r.Offset(4, 2))
s.XValues = Range(r.Offset(0, 0), r.Offset(4, 0))

End Sub

 

[TheWizEd]

I think there is something wrong with Excel 2007. Trying to creat charts
through VBA, Excel will crash with error code 80010108. "Automation error.
The object invoked has disconnected from its clients."

I've been programming with Excel VBA for years and have never encountered
such a fatal error.

 

[TheWizEd]

Well, through a lot of trial and error I figured a work around for the
SeriesCollection problem and the fatal Excel crash.

First regarding the crash, it turns out if the Excel tab is set to a chart
and you delete it through VBA, Excel crashes. In my subroutine that deletes
old charts I simply change the focus to a sheet first thats not being deleted.

Regarding the SeriesCollection I developed a subroutine that clears the
SeriesCollection and either returns the first series or creates a new one.
SeriesCollection of a new chart is unpredicable. Some times it starts with
one series, sometimes many and sometimes none.

Private Function GetNewSeries(cChart As Chart) As Series
Dim i As Integer
For i = cChart.SeriesCollection.Count To 2 Step -1
cChart.SeriesCollection(i).Delete
Next
If cChart.SeriesCollection.Count = 1 Then
Set GetNewSeries = cChart.SeriesCollection(1)
Else
Set GetNewSeries = cChart.SeriesCollection.NewSeries
End If
End Function