Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Excel 2007 Beta 2 chart problems

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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Excel 2007 Beta 2 chart problems

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
_______


"Graham F" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Excel 2007 Beta 2 chart problems

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


"Jon Peltier" wrote:

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
_______


"Graham F" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 33
Default Excel 2007 Beta 2 chart problems

might help

http://technet2.microsoft.com/Office....mspx?mfr=true

or (same)

http://tinyurl.com/k3hp6


"Graham F" wrote in message
...
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel chart does not print in Office 2007 Ngalu Charts and Charting in Excel 7 September 5th 12 11:20 PM
Excel 2007 Beta Cut and Paste Bobm Excel Discussion (Misc queries) 1 June 26th 06 01:07 AM
Web toolbar in Excel 2007 beta skat Excel Worksheet Functions 0 June 13th 06 02:54 PM
How can I save a file in Excel 2007 beta 2 as a .dbf extension? Darren Crewes Excel Discussion (Misc queries) 1 June 3rd 06 11:31 PM
Excel 2007 copy chart as picture ward376 Charts and Charting in Excel 3 June 2nd 06 09:48 PM


All times are GMT +1. The time now is 12:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"