Excel 2007 Beta 2 chart problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham F - 08 Aug 2006 16:17 GMT

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

Reply to this Message

Jon Peltier - 11 Aug 2006 15:16 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> 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
[quoted text clipped - 35 lines]
> ch.SeriesCollection.Count
> End Sub

Reply to this Message

Graham F - 25 Aug 2006 16:35 GMT

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

> In 97-2003, you can get different initial series counts when adding a chart.
> I always initialize the new chart with code like this:
[quoted text clipped - 63 lines]
> > ch.SeriesCollection.Count
> > End Sub

Reply to this Message

David Cox - 14 Sep 2006 01:28 GMT

might help

http://technet2.microsoft.com/Office/en-us/library/ba60fda8-81eb-48c4-a615-3f978
6803acb1033.mspx?mfr=true

or (same)

http://tinyurl.com/k3hp6

Reply to this Message