ChartObjects Insanity - Excel 2007

[Spiggy Topes]

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.

 

[Peter T]

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 Where:=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]

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.

 

[Robert Flanagan]

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

 

[Martin Brown]

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

 

[Peter T]

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

 

[Peter T]

typo (cnt - 1) !

should of course be

ws.chartobjects(cnt + 1).delete

Peter T

 

[Spiggy Topes]

Thanks to all. I have to agree, it's very much a turn for the worse. I
have what essentially is a system built in VBA, 2,950 lines of code
that imports CSV data, builds charts, colours maps, adds imagemap
capability, mails the results out to a distribution list from an .ini
file, sings, dances and plays Yankee Doodle on the penny whistle. I
exaggerate, slightly. But so far I have:

- Excel hangs frequently. No workbooks open, just trying to close, and
it hangs;
- goActChart.Shapes.AddPicture(TEMPLATE_DIR & "\Back_Button.gif",
msoFalse, msoTrue, 1, 1, -1, -1).Select works, but says it failed. Hit
F5 to continue, it adds another and still says it failed;
- Setting page size for a chart sheet sometimes works. Other times,
charts and images are horizontally compressed to around half size.
Often this happens many sheets in; once it does, it stays that way for
all subsequent sheets;
- A workaround for this - chto.Chart.ChartArea.Width = chto.Width -
sometimes works, sometimes errors out.
- As previously stated, deleting a chart doesn't delete it, but
doesn't give an error, so when I later run through the
SeriesCollection entries for all charts, I get subscript out of bounds
errors;
- Deleting sheets doesn't work if I use ch.ChartObjects(3).Delete
- but does if I use ch.ChartObjects(ch.ChartObjects(3).Name).Delete
- The macro recorder doesn't record the important parts at all. I
record macros sometime to determine accurate positioning, but now if I
select and drag an element to where I want it, it records the select,
but not the positioning; I've also recorded simple macros that produce
errors if I try to run them;
- The help is no longer helpful. It used to be you'd highlight a word
and the help would pop up for that word. Now, I just get a menu page.

I have workarounds for some of these, but it seems even the
workarounds need workarounds. This is truly the crappiest piece of
garbage I've yet to come across from Microsoft.

I want my 2003 back. But the word I get from the tech support folks is
that it can't be done. The web thinks otherwise, but there may be
factors I'm not aware of. Anyone know if there's a good reason why
Excel 2003 and 2007 can't peacefully coexist?