Chart Control

[Bill Martin]

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

 

[Dan Thompson]

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]

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

 

[JLGWhiz]

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]

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]

Did you cross post in the Charting news group? I am sure somebody there
could help.

 

[Bill Martin]

No, that's not a group I was aware of. I'll check it out.

Thanks.

Bill
-------------------------

 

[JLGWhiz]

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]

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

 

[Jon Peltier]

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

 

[Bill Martin]

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]

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-06-ACNJ/AdvExcelConf200906ACNJ.html
_______