Excel 2007 Beta 2 chart problems

[Graham F]

When I run the macro shown below on a worksheet with columns of data (with
column headings in row 1) I get some unusual results in Excel 2007 Beta 2
(different from Excel 97-2003). In Excel 97-2003, the Chart has 0 series
before SeriesCollection.Add and 1 Series after SeriesCollection.Add. In
Excel 2007, the Chart sometimes has multiple series before
SeriesCollection.Add and one additional series after SeriesCollection.Add; it
seems to depend on which cell has focus when the macro is run. Similarly for
xlXYScatterLines charts where the Source is the Union of two column ranges.
There may be other problems in Excel 2007 with chart-generating macros that
work in Excel 97-2003. For example, with "real" data, I get a "Microsoft
Visual Basic "Run-time error '5'", "Invalid procedure call or argument""
error on SeriesCollection.Add calls. I have not been able to determine if
this is a side-effect of the problem described above or a different problem.

Is this a known problem area in Excel 2007, or should I expect to have to
change macro code to work with Excel 2007? I there an update to Excel 2007
Beta 2 that might resolve these problems?

Graham

Sub TestLineChart()
Dim ch As Chart
Dim rng As Range

Set ch = ActiveSheet.ChartObjects.Add(100, 100, 200, 200).Chart
ch.ChartType = xlLine
MsgBox "# series before SeriesCollection.Add: " &
ch.SeriesCollection.Count
Set rng = ActiveSheet.Range("A1:A6")
ch.SeriesCollection.Add Source:=rng, SeriesLabels:=True
MsgBox "# series after SeriesCollection.Add: " & ch.SeriesCollection.Count
End Sub

 

[Jon Peltier]

In 97-2003, you can get different initial series counts when adding a chart.
I always initialize the new chart with code like this:

for i = ch.SeriesCollection.Count to 1 step -1
ch.SeriesCollection(i).Delete
next

Now there are no series, and I'm starting fresh.

Then I don't use SeriesCollection.Add, rather I use
SeriesCollection.NewSeries:

With ch.SeriesCollection.NewSeries
.Values = <some range>
.XValues = <some other range>
End With

I find these help with 97-2003. I have not been able to test all of these
things in the Beta. Not everything works as expected yet, but presumably
they're working to restore compatibility.

- Jon

 

[Graham F]

Jon,

Thank you for the suggestions. They are reasonable and useful. However,
they do not resolve the problems I reported. Specifically, with Excel 2007
Beta 2, assigning a *Range* to the Values or XValues of the NewSeries results
in "Run-time error '1004'", "Application-defined or object-defined error".
Assigning an Array to the Values or XValues seems to work better.

I think I will have to wait and hope for restored compatibility in the
production release

Graham

 

[David Cox]

might help

http://technet2.microsoft.com/Offic...81eb-48c4-a615-3f9786803acb1033.mspx?mfr=true

or (same)

http://tinyurl.com/k3hp6