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

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 A1:D1 & A8:D12 (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" wrote in message
...
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

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

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" wrote in message
...
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

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

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


"Brian Murphy" wrote in message
...
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

typo

' data in A1:D1 & A8:D12 (x-values in colA, y's in the rest)

A1:D1 should be A2:D4
so put some data in A2:D4 and A8:d12

Peter T


"Peter T" <peter_t@discussions wrote in message

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

' data in A1:D1 & A8:D12 (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

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

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

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

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

"Brian Murphy" wrote in message
...
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

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



"Peter T" <peter_t@discussions wrote in message
...
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

"Brian Murphy" wrote in message
...
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

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

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" wrote in message
...
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

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