Chart formula style depends on number of sheets....

[PaulRudenko]

I was reading 7 formulas from a seriescollection in VBA (xlbubble
chart) . First four gave me A1-style formula, last three gave me R1C1
style formula. This messed up subsequent processing.

I used: ActiveChart.SeriesCollection(SeriesIndex).Formula

Nothing changed while running this from index 1 to 7.

Cause of this wonderful behaviour?

The last three chart series were added while the workbook had only one
sheet.
The first four were added while the workbook had more than 1 sheet.

Solution: add a second sheet to the workbook. All fomulas now come in
A1 style...

Very weird. Can't imagin this is by design. (Excel 2000 9.0.8948 SP-3)


Van: Charlie - profiel weergeven
Datum: vr 13 dec 2002 17:58
E-mailadres: "Charlie" <[email protected]>
Discussiegroepen: microsoft.public.excel.charting

I have a scatter chart that I add points to existing
series.

I then parse the A1 chart formula to get the associated
range..
Everything works great up to around the 7-8 additional
point. then the formula(s),formulalocal, r1c1.. ALL CHANGE
TO R1C1 STYLE..

can anyone explain this or have a solution? a
recomendation (besides switching to third party charting
solution)

regards
Charlie B

 

[Jon Peltier]

Strange. I get something different. It doesn't matter what the state was
when the series were created. What matters is how many worksheets the
workbook contains when I get the formula. If there is a single sheet, the
workbook name is used in the range references; if there are two, the
workbook name is omitted. I never get R1C1 notation.

' one worksheet
for i=1 to activechart.SeriesCollection.count : debug.Print
activechart.SeriesCollection(i).formula : next
=SERIES([Book2]Sheet1!$B$5,[Book2]Sheet1!$A$6:$A$10,[Book2]Sheet1!$B$6:$B$10,1,[Book2]Sheet1!$C$6:$C$10)
=SERIES([Book2]Sheet1!$F$5,[Book2]Sheet1!$E$6:$E$10,[Book2]Sheet1!$F$6:$F$10,2,[Book2]Sheet1!$G$6:$G$10)
=SERIES([Book2]Sheet1!$B$14,[Book2]Sheet1!$A$15:$A$19,[Book2]Sheet1!$B$15:$B$19,3,[Book2]Sheet1!$C$15:$C$19)

two worksheets
for i=1 to activechart.SeriesCollection.count : debug.Print
activechart.SeriesCollection(i).formula : next
=SERIES(Sheet1!$B$5,Sheet1!$A$6:$A$10,Sheet1!$B$6:$B$10,1,Sheet1!$C$6:$C$10)
=SERIES(Sheet1!$F$5,Sheet1!$E$6:$E$10,Sheet1!$F$6:$F$10,2,Sheet1!$G$6:$G$10)
=SERIES(Sheet1!$B$14,Sheet1!$A$14:$A$19,Sheet1!$B$14:$B$19,3,Sheet1!$C$14:$C$19)

- Jon