Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel chart does not print in Office 2007 | Charts and Charting in Excel | |||
Excel 2007 Beta Cut and Paste | Excel Discussion (Misc queries) | |||
Web toolbar in Excel 2007 beta | Excel Worksheet Functions | |||
How can I save a file in Excel 2007 beta 2 as a .dbf extension? | Excel Discussion (Misc queries) | |||
Excel 2007 copy chart as picture | Charts and Charting in Excel |