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 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" <(E-Mail Removed)> wrote in message
news:61410d23-69ab-4b36-9578-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:ced513df-7f7d-4246-9979-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:b52975de-3a9d-45a2-bbbf-(E-Mail Removed)...
>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 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


"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 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
>

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