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

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

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.

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

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.

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.

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.

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.

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

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

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

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