| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart Control
I'm trying to change a chart in Excel 2003 using VBA. Unfortunately
there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
Bill Im not sure if this is the problem but i noticed that
ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" and ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" Are Two seperate Series charting exactly the same column of data perhaps that is causing some problems. each of the other series are charting there own seperate columns Dan. "Bill Martin" wrote: I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
That's something that was just stumbled into as I kept running and changing
things Dan. When it runs, it doesn't care about that. When it doesn't run, it seems to care about *something*. Bill ----------------------------- Dan Thompson wrote: Bill Im not sure if this is the problem but i noticed that ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" and ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" Are Two seperate Series charting exactly the same column of data perhaps that is causing some problems. each of the other series are charting there own seperate columns Dan. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
I usually create my charts manually for the basic structure, but I notice
that you have the same data range applied to five different Series. That could be confusing the compiler, since the series number tells which oder to plot. "Bill Martin" wrote in message ... I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
No, I throw the duplicate out and it still fails.
I've created the chart manually, but I'm just trying to program a button that will allow me to plot data from whatever sheet I specify - rather than creating another chart on every sheet. Bill ------------------------- JLGWhiz wrote: I usually create my charts manually for the basic structure, but I notice that you have the same data range applied to five different Series. That could be confusing the compiler, since the series number tells which oder to plot. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
Did you cross post in the Charting news group? I am sure somebody there
could help. "Bill Martin" wrote in message ... No, I throw the duplicate out and it still fails. I've created the chart manually, but I'm just trying to program a button that will allow me to plot data from whatever sheet I specify - rather than creating another chart on every sheet. Bill ------------------------- JLGWhiz wrote: I usually create my charts manually for the basic structure, but I notice that you have the same data range applied to five different Series. That could be confusing the compiler, since the series number tells which oder to plot. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
No, that's not a group I was aware of. I'll check it out.
Thanks. Bill ------------------------- JLGWhiz wrote: Did you cross post in the Charting news group? I am sure somebody there could help. "Bill Martin" wrote in message ... No, I throw the duplicate out and it still fails. I've created the chart manually, but I'm just trying to program a button that will allow me to plot data from whatever sheet I specify - rather than creating another chart on every sheet. Bill ------------------------- JLGWhiz wrote: I usually create my charts manually for the basic structure, but I notice that you have the same data range applied to five different Series. That could be confusing the compiler, since the series number tells which oder to plot. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
If you are using the web news reader for MS pulic, in the left panel where
is lists the various applications, just mouse over Excel and a pop-up menu will appear with the various groups associated with Excel. You will see Charts on there and can just click it to get into it. "Bill Martin" wrote in message ... No, that's not a group I was aware of. I'll check it out. Thanks. Bill ------------------------- JLGWhiz wrote: Did you cross post in the Charting news group? I am sure somebody there could help. "Bill Martin" wrote in message ... No, I throw the duplicate out and it still fails. I've created the chart manually, but I'm just trying to program a button that will allow me to plot data from whatever sheet I specify - rather than creating another chart on every sheet. Bill ------------------------- JLGWhiz wrote: I usually create my charts manually for the basic structure, but I notice that you have the same data range applied to five different Series. That could be confusing the compiler, since the series number tells which oder to plot. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
Problem resolved... For reasons that I don't understand, if a column of data is
blank then I get the problem that I've described. Testing in advance for the presence of data, and dealing with it accordingly allows me to avoid it. Bill ----------------------- Bill Martin wrote: I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
One way to deal with this problem is to temporarily change the chart type of
the series to area or column, then back to line or XY when you've changed the values. This only affects line and XY charts, when the existing values or xvalues range contains all blanks or unplottable errors (in other words, if the series doesn't appear, you'll have this problem). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Bill Martin" wrote in message ... Problem resolved... For reasons that I don't understand, if a column of data is blank then I get the problem that I've described. Testing in advance for the presence of data, and dealing with it accordingly allows me to avoid it. Bill ----------------------- Bill Martin wrote: I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
Thanks Jon. The route I took was to check for the data first, and if it does
not exist then I dump in some blank data outside the normal range. A kludge, but it works. Bill ------------------------------ Jon Peltier wrote: One way to deal with this problem is to temporarily change the chart type of the series to area or column, then back to line or XY when you've changed the values. This only affects line and XY charts, when the existing values or xvalues range contains all blanks or unplottable errors (in other words, if the series doesn't appear, you'll have this problem). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Bill Martin" wrote in message ... Problem resolved... For reasons that I don't understand, if a column of data is blank then I get the problem that I've described. Testing in advance for the presence of data, and dealing with it accordingly allows me to avoid it. Bill ----------------------- Bill Martin wrote: I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chart Control
Sometimes this works, and sometimes, for instance if formulas are producing
the data, it will hose the formulas. But if that were the case, you'd have found another approach. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Bill Martin" wrote in message ... Thanks Jon. The route I took was to check for the data first, and if it does not exist then I dump in some blank data outside the normal range. A kludge, but it works. Bill ------------------------------ Jon Peltier wrote: One way to deal with this problem is to temporarily change the chart type of the series to area or column, then back to line or XY when you've changed the values. This only affects line and XY charts, when the existing values or xvalues range contains all blanks or unplottable errors (in other words, if the series doesn't appear, you'll have this problem). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Bill Martin" wrote in message ... Problem resolved... For reasons that I don't understand, if a column of data is blank then I get the problem that I've described. Testing in advance for the presence of data, and dealing with it accordingly allows me to avoid it. Bill ----------------------- Bill Martin wrote: I'm trying to change a chart in Excel 2003 using VBA. Unfortunately there's some non-reproducible error that's driving me crazy. I hope someone here recognizes it. What I did was to simply record a macro, and go through manually resetting the chart settings. Then I copied that code to my program and changed it a bit to have more flexibility. The thing is that it works perfectly - mostly. Then it will go into stretches of crashing every time I run it with an error message: "Run-time error '1004': Unable to set the XValues property of the Series class" It does not seem to be my "improvements" to the code which cause the problem since when my code starts to fail, the original code that the macro recorder created also fails at the same point. Attached is the code which runs and/or fails. At the moment it fails at the line marked with '**** It's frustrating that all the lines above, which are essentially identical, run ok until it hits the (3).XValues line. Or sometimes that works and the (4).Values line might fail instead. Sub MacroRecorderCreated() ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5" ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8" ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '**** ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12" ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11" ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1" ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11" ActiveWindow.Visible = False Windows("Stochasitcs2.xls").Activate Range("J13").Select End Sub Does any of this look familiar? Thanks. Bill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Process control chart for quality control in exel? | Charts and Charting in Excel | |||
| Slider control in chart | Charts and Charting in Excel | |||
| ActiveX control with chart | Charts and Charting in Excel | |||
| API for MS Chart Control | Charts and Charting in Excel | |||
| ActiveX control for chart | Excel Programming | |||
![Bill Martin[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
