Either one of us - me or Excel - is insane, or soon to become so. I
have code I'm trying to convert from Excel 2003, and it's not going
smoothly. Here's the current problem.

I have a workbook with a number of chart sheets, each containing three
charts. I want to delete the third chart from every sheet. I used to
say..

For Each ch In wb.Charts
.....
ch.ChartObjects(3).Delete

Next ch

Now, that tells me I have a subscript out of range. I check, and ?
ch.ChartObjects.count returns a value of 3. I can return the name on
the third chart too, so I try

ch.ChartObjects(ch.ChartObjects(3).Name).Delete

It works. Huh? So it's nuts, but it works. Not quite. It doesn't
produce an error, but it doesn't delete the chart completely either.
It leaves an empty chart box on the page. So I start again and record
a macro just to see what Excel would do. It It comes up with

ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Parent.Delete

I adapt to fit my code and try it, and the whole sheet gets deleted.

Please, someone save my sanity and tell me where I'm going wrong.

Either one of us - me or Excel - is insane,

I confess at first I thought it was you, now I see it is Excel!

Looks like the only way in 2007 is to move the chartobject from the
chart-sheet to a normal worksheet, then delete it. Try this -

Sub Test()
' kludge for Excel 2007 -
' to delete the first chartobject in each chart-sheet

Dim wsTemp As Worksheet, shtOrig As Object
Dim chtSht As Chart
Dim cht As Chart

Set shtOrig = ActiveSheet

Application.ScreenUpdating = False
Set wsTemp = ActiveWorkbook.Worksheets.Add

For Each chtSht In ActiveWorkbook.Charts
Set cht = chtSht.ChartObjects(1).Chart ' << change, eg 3
cht.Location Whe=xlLocationAsObject, Name:=wsTemp.Name
wsTemp.ChartObjects(1).Delete
Next

Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True

shtOrig.Activate
Application.ScreenUpdating = True

End Sub

You will want to change ChartObjects(1). to ChartObjects(3).


Regards,
Peter T


"Spiggy Topes" wrote in message
...
Either one of us - me or Excel - is insane, or soon to become so. I
have code I'm trying to convert from Excel 2003, and it's not going
smoothly. Here's the current problem.

I have a workbook with a number of chart sheets, each containing three
charts. I want to delete the third chart from every sheet. I used to
say..

For Each ch In wb.Charts
....
ch.ChartObjects(3).Delete

Next ch

Now, that tells me I have a subscript out of range. I check, and ?
ch.ChartObjects.count returns a value of 3. I can return the name on
the third chart too, so I try

ch.ChartObjects(ch.ChartObjects(3).Name).Delete

It works. Huh? So it's nuts, but it works. Not quite. It doesn't
produce an error, but it doesn't delete the chart completely either.
It leaves an empty chart box on the page. So I start again and record
a macro just to see what Excel would do. It It comes up with

ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Parent.Delete

I adapt to fit my code and try it, and the whole sheet gets deleted.

Please, someone save my sanity and tell me where I'm going wrong.

Thanks for that, it's greatly appreciated. Very, very kludgey,
though.. Excel 2007 seems to be a really poor substitute for 2003. The
help functionality is much less usable, functionality has just
disappeared (FileSearch, for instance), I have errors that go away as
soon as I enter debug, and crazy things with chart width that seem to
go away if I turn ScreenUpdating off and then immediately on again.
Nice work there, Microsoft.

Spiggy, I've seen the same. Going into debug is the same as turning
screenupdating on. It seems it is needed on when one creates or modifies
charts.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Spiggy Topes" wrote in message
...
Thanks for that, it's greatly appreciated. Very, very kludgey,
though.. Excel 2007 seems to be a really poor substitute for 2003. The
help functionality is much less usable, functionality has just
disappeared (FileSearch, for instance), I have errors that go away as
soon as I enter debug, and crazy things with chart width that seem to
go away if I turn ScreenUpdating off and then immediately on again.
Nice work there, Microsoft.

Spiggy Topes wrote:
Thanks for that, it's greatly appreciated. Very, very kludgey,
though.. Excel 2007 seems to be a really poor substitute for 2003. The

It is a crock of the proverbial even after applying the service packs.
It was unusable out of the box for anything remotely non-trivial.

My favourite cosmetic bug was two ticks for 10^8 on a log Y scale.

help functionality is much less usable, functionality has just
disappeared (FileSearch, for instance), I have errors that go away as
soon as I enter debug, and crazy things with chart width that seem to
go away if I turn ScreenUpdating off and then immediately on again.
Nice work there, Microsoft.

A some of the chart faults that vanish in debug mode are down to race
conditions. You may also find if charts are drawn by VBA code that
certain actions must not occur before the chart has instantiated and
been fully initialised or chaos will reign. It is glacially slow on
moderate sized datasets when compared with 2003 and still more than an
order of magnitude slower after the fixpacks (it was even worse before
that).

Regards,
Martin Brown

Very, very kludgey,

It could be done with slightly less of a kludge if you use an existing
worksheet rather than adding a temporary one. If you do, start with

cnt = ws.chartobjects.count
' loop and move the chartobject
ws.chartobjects(cnt - 1).delete

Should be fine except the object counter increments each time, and never
decrements when an object is deleted (unless the file is saved/closed with
no objects on the sheet). The large object counter is not bad, only
cosmetic.

As for 2007 charts in general, hmm.....

Regards,
Peter T



"Spiggy Topes" wrote in message
...
Thanks for that, it's greatly appreciated. Very, very kludgey,
though.. Excel 2007 seems to be a really poor substitute for 2003. The
help functionality is much less usable, functionality has just
disappeared (FileSearch, for instance), I have errors that go away as
soon as I enter debug, and crazy things with chart width that seem to
go away if I turn ScreenUpdating off and then immediately on again.
Nice work there, Microsoft.

typo (cnt - 1) !

should of course be

ws.chartobjects(cnt + 1).delete

Peter T


"Peter T" <peter_t@discussions wrote in message

It could be done with slightly less of a kludge if you use an existing
worksheet rather than adding a temporary one. If you do, start with

cnt = ws.chartobjects.count
' loop and move the chartobject
ws.chartobjects(cnt - 1).delete