charting run time error, Excel 2007 SP2 problem with SeriesCollectionand .Formula property

[Brian Murphy]

I have a very old routine which has a problem with SP2 that doesn't
happen with SP1 or any excel version before 2007.

I use a pair of statements like this to set plotted chart data
..Item(i).Values = Range(...)
..Item(i).XValues = Range(...)

Right after this I use
s = .Item(i).Formula
to get the formula into a string. The formula string looks ok except
the spots for XValues and Values are empty. In the debugger I put a
Debug.Print right before the above statement, and the result has those
two spots empty. It looks like this:
=SERIES('Sheet 1'!$C$1,,,1)
If I set a breakpoint, and rerun the Print statement a second time but
from inside the Debugger, the full correct formula is printed.
If I put in Application.ScreenUpdating = True before all this, this
solves the Formula problem, but makes the macro run horribly slow.
Excel 2007 already runs this macro way slower than Excel 2003.

Does anyone know anything about this problem, and have any ideas what
to do about it?

Thanks,

Brian

 

[Peter T]

Indeed charts in 2007 are annoyingly odd in many respects however I couldn't
recreate the problem with the following -

Sub test()
Dim i As Long
Dim rng As Range
Dim cht As Chart
Dim sr As Series

' data in A11 & A812 (x-values in colA, y's in the rest)

' ActiveSheet.ChartObjects.Delete
On Error GoTo errH

Application.ScreenUpdating = False
Set cht = ActiveSheet.ChartObjects.Add(100, 200, 300, 200).Chart

Set rng = Range("a2:a4")
For i = 1 To 3
Set sr = cht.SeriesCollection.NewSeries
sr.XValues = rng
sr.Values = rng.Offset(, i)
sr.XValues = rng
Debug.Print sr.Formula
Next

Debug.Print
Set rng = Range("A8:A12")

With cht.SeriesCollection
For i = 1 To 3
.Item(i).Values = rng.Offset(, i)
.Item(i).XValues = rng
Debug.Print .Item(i).Formula
Next
End With
done:
Application.ScreenUpdating = True
Exit Sub

errH:
Debug.Print Err.Description
Resume done
End Sub


Maybe post your own code if fundamentally different. Otherwise try a
DoEvents or should be easy enough to recreate the formula without returning
it from the series object.

Regards,
Peter T

 

[Brian Murphy]

What I'm doing is pretty much just what you showed.

Putting in a DoEvents didn't make any difference.
As you suggested, I have been able to workaround my immediate problem
by reworking it to eliminate the need to fetch the .Formula property.

I ran into a similar problem in another bit of code that was assigning
the .Values property to a Range object, and subsequent actions on the
data series threw up run time errors. Appending .Address to the Range
object cleared that problem. I went back and tried .Address on my
first problem expecting it to work there, too, but it didn't.

Brian

 

[Peter T]

Presumably there must be something significantly different between what you
are doing and the little demo I posted. Maybe if you can isolate that it'd
solve your problem.

I went back and tried .Address on my
first problem expecting it to work there, too, but it didn't.

I don't quite follow, are you saying you can't return the address property
from the range object. What doesn't work.

Regards,
Peter T

 

[Brian Murphy]

I do something like this
.Values = Range("blahblahblah")

This works, and the chart displays the right data,
but then doing
s = .Formula
returns a string with nothing between the commas for the Values
argument.
If I stick in an Application.ScreenUpdating = True
then the formula string does contain what it should.
By the way, calculation is set to automatic.

How about this!
I copied your routine to an empty workbook.
Put some data in the worksheet cells.
And ran your routine.
The resulting chart looks right,
but this was in the immediate window

=SERIES(,,,1)
=SERIES(,,,2)
=SERIES(,,,3)

=SERIES(,,,1)
=SERIES(,,,2)
=SERIES(,,,3)

In your routine this is harmless.

My routine throws an error when it tries to reset the .Formula
property with empty arguments extracted from the incorrect .Formula
string.

Brian

 

[Peter T]

I get the following as expected

=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$C$2:$C$4,2)
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$D$2:$D$4,3)

=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$B$8:$B$12,1)
=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$C$8:$C$12,2)
=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$D$8:$D$12,3)

2007 SP1, calc automatic, in a new workbook

Seems there's something different between our respective setups. Could you
double check in a new instance and confirm you only get =SERIES(,,,1) etc
with my sample routine

Advise your specs and I'll seek opinion off-line

Regards,
Peter T

 

[Peter T]

typo

' data in A11 & A812 (x-values in colA, y's in the rest)

A11 should be A24
so put some data in A24 and A8:d12

Peter T

 

[Brian Murphy]

Hello Peter,

I use VMWare Workstation 6.5.2 to do my xl2007 testing

In one session I have Vista and Excel 2007 SP1
This produces the expected result as follows
?application.version, application.Build
12.0 6214
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$C$2:$C$4,2)
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$D$2:$D$4,3)

=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$B$8:$B$12,1)
=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$C$8:$C$12,2)
=SERIES(,Sheet1!$A$8:$A$12,Sheet1!$D$8:$D$12,3)

In another session I have Vista and xl2007SP2. Here's what I get
?application.version, application.Build
12.0 6425
=SERIES(,,,1)
=SERIES(,,,2)
=SERIES(,,,3)

=SERIES(,,,1)
=SERIES(,,,2)
=SERIES(,,,3)
If I step through the routine slowly in the debugger is runs ok and
prints the expected result.
If I step through it quickly there will be some, but not all, of the
empty arguments.

This is really weird.
This weirdness is not unique to my computer.
It is quite definitely happening to at least two of my customers.
It seems to be unique to xl2007 SP2.

Brian

 

[Peter T]

Hi Brian,

Andy Pope has reproduced the incomplete Series formula with the same demo in
SP2. As a workaround he suggests to do

mySeries.Values = rng
cht.Refresh
theFormula = mySeries.Formula

It might be worth changing all Series first, then do the cht.Refresh, then
return all formulas. Of course that would require amending code to include
two loops

The same fix should work in Excel 2010 (should it persists into the release
version)

It's been reported as a bug.

Regards,
Peter T

 

[Brian Murphy]

Thank you, Peter (and Andy, too).

I was unaware of the chart Refresh method. I will give it a try and
report back.

In my testing I've only been able to trigger this problem with SP2.
All of my own testing with SP1 has been trouble free. However, one
customer with this problem has reported this morning that he is using
SP1. I'll need to confirm that, though, because I'm pretty sure he
has been trying both.

Is running Office in a VM any different than not using a VM? I'm
using VMWare Workstation with the VM running Vista.

Thanks,

Brian

 

[Peter T]

Is running Office in a VM any different than not using a VM?

I don't know for sure though I assume in theory there shouldn't be any
difference, at least not with this issue. I wouldn't be at all surprised if
Andy tested in a VM (I didn't ask), how about your other users.

Did you confirm if your user is having a problem with SP1, if so I'll have
to amend the bug report I submitted!

I tested in two Excel SP1 setups (XP & Vista, not VM) and both worked fine.
I'll update one of them to SP2 later this week and re-test.

Regards,
Peter T

 

[Peter T]

I tested in two Excel SP1 setups (XP & Vista, not VM) and both worked

fine. I'll update one of them to SP2 later this week and re-test.

Just updated from SP1 to SP2. Now I too get the incomplete Series formulas!

Regards,
Peter T

 

[Brian Murphy]

This getting stranger by the minute.
I confirmed that my user is using SP1.
I just did more testing in SP1 with my application, and for the first
time I have a 100% repeatable case of getting empty .Formula
arguments.
Here is the code, it adds a series to an existing chart:
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection
(ActiveChart.SeriesCollection.Count).Select
Set theseries = Selection
With theseries
.AxisGroup = xlSecondary
.XValues = Array(maxCOS, maxCOS, maxCOS)
.Values = yarr 'this is a cell range object
'ActiveChart.Refresh
n = val(GetSeriesRange(theseries, "n"))
sx = GetSeriesRange(theseries, "x")
sy = GetSeriesRange(theseries, "y")
MsgBox n & sx & sy
.Formula = "=SERIES(""MaxCOS""," & sx & "," & sy & ","
& n & ")"

The function GetSeriesRange is my function that simply parses
the .Formula property and returns a string for the designated
argument. The MsgBox displays the correct value for the series number
n, but blanks for sx and sy. If I step through this code in the
debugger, sx and sy are their proper values.

Fortunately, uncommenting the .Refresh line cures this problem with no
apparent affect on performance.

This code runs without any trouble in SP2. I have no idea why.

One other xl2007 specific thing that turned up in the above code is
that the .AxisGroup line causes the Selection to change from Series to
ChartArea in SP1 but not in SP2!

These differences in behavior in between SP1 and SP2 are really
baffling. They each now seem to have their own distinct problems.

Brian

 

[Peter T]

I have a 100% repeatable case of getting empty .Formula

arguments.

Your code snippet in isolation doesn't show anything useful.
Could you post an examples we can reproduce to illustrate the what you've
described, also indicate where you anticipate results will be different in
SP1 and SP2.

Regards,
Peter T

 

[Brian Murphy]

XL2007 SP1 and SP2 behave differently with the .AxisGroup =
xlSecondary when it comes what is Selected after the statement is
executed.

Since ActiveChart.Refresh has solved my problem with blank Formula
fields, I've moved on to other problems. There seems to be no end to
them lately.

Brian