Charts are hard / found a BUG in ChartObject behavior

[Dave M]

Found another chart bug while running my 3800-line data visualization
Excel/VBA program today. In the course of six years fiddling with VBA in
Excel, I've concluded that programmatic acess to charts is by far the biggest
source of trouble in Excel/VBA programming. Since I can't find any other
Internet-based venue for submission of bugs to Microsoft, I'll post this bug
here. But first, I'll share a few of my other discoveries of weird chart
behavior, so that others will not be driven to beat their heads against their
desks as I have done.

Tip 1:
The size in screen coordinates of axis titles is incorrect until the axes
are actually drawn on-screen. This means that screen updating must be
enabled before getting these sizes.

Tip 2 (the Golden Rule):
You can't work with chart series if the series is not visible (as is the
case if the linked cells are all empty, for example). Golden rule: set
chart.DisplayBlanksAs=xlZero before changing the datasets of a chart series.

Tip 3:
In general, you can't programmatically access chart elements that can not be
displayed (for example, you can't set the title of a completely empty chart).

Tip 4:
When moving the PlotArea within the ChartArea (by setting position of the
top left corner), don't allow the bottom or left edges of the PlotArea to be
moved outside the confines of the ChartArea. If you try, an error will be
raised.

And now, today's bug. When applying a Name to a ChartObject, strange things
happen if that name contains any punctuation except for the underscore, or
contains certain, but not all, of the extended ASCII characters, or contains
a space. If you provide such a name then you will find that that particular
chart is NOT RETURNED when iterating over the ChartObjects collection by
using the For Each mechamism, even though ChartObjects.Count will show the
correct number of charts. In fact, in a running program,you can add a watch
on ChartObjects, expand it, and see that Count is correct, whereas the listed
Chart nodes will be missing the ones that were assigned names as described
above. The solution is to address each chart by its index, using
Chartobjects.Count and a traditional For loop. Very weird.

Okay, one more bug workaround I'm dying to share. When pasting Office
tables, charts, graphics, etc. as metafiles, one often finds that colored
text, and certain other colored graphical elements, are shown as black in the
pasted metafile graphic. Fix this problem by pretending to print to a color
printer before doing the cut and paste (go through the print dialogs,
selecting a color printer, then cancel the print dialog).

Thanks,
Dave

 

[Dave M]

I forgot to add that I found this bug in Office 2003, at least Service Pack
1. I'm not at work now, so I can't check.

Found another chart bug while running my 3800-line data visualization

....

 

[Jon Peltier]

Found another chart bug while running my 3800-line data visualization
Excel/VBA program today. In the course of six years fiddling with VBA in
Excel, I've concluded that programmatic acess to charts is by far the
biggest
source of trouble in Excel/VBA programming. Since I can't find any other
Internet-based venue for submission of bugs to Microsoft, I'll post this
bug
here. But first, I'll share a few of my other discoveries of weird chart
behavior, so that others will not be driven to beat their heads against
their
desks as I have done.

Tip 1:
The size in screen coordinates of axis titles is incorrect until the axes
are actually drawn on-screen. This means that screen updating must be
enabled before getting these sizes.

The size of axis titles, chart titles, and data labels is not even exposed
to VBA. But there are workarounds.

Tip 2 (the Golden Rule):
You can't work with chart series if the series is not visible (as is the
case if the linked cells are all empty, for example). Golden rule: set
chart.DisplayBlanksAs=xlZero before changing the datasets of a chart
series.

This is true of certain properties of marker-based chart series (i.e., line
and XY, not column, bar, area). Rather than DisplayBlanksAs, which is not a
reliable workaround, temporarily convert the series to area while working on
it, then convert it back.

Tip 3:
In general, you can't programmatically access chart elements that can not
be
displayed (for example, you can't set the title of a completely empty
chart).

Not surprising, is it? A completely empty chart has no title.

Tip 4:
When moving the PlotArea within the ChartArea (by setting position of the
top left corner), don't allow the bottom or left edges of the PlotArea to
be
moved outside the confines of the ChartArea. If you try, an error will be
raised.

You get an error? In my experience (and in the test I just ran) the plot
area moves or resizes only as far as it will go, without an error.

And now, today's bug. When applying a Name to a ChartObject, strange
things
happen if that name contains any punctuation except for the underscore, or
contains certain, but not all, of the extended ASCII characters, or
contains
a space. If you provide such a name then you will find that that
particular
chart is NOT RETURNED when iterating over the ChartObjects collection by
using the For Each mechamism, even though ChartObjects.Count will show the
correct number of charts. In fact, in a running program,you can add a
watch
on ChartObjects, expand it, and see that Count is correct, whereas the
listed
Chart nodes will be missing the ones that were assigned names as described
above. The solution is to address each chart by its index, using
Chartobjects.Count and a traditional For loop. Very weird.

Another solution is to use recognized characters in the chart object name.
You need to watch out what characters are used in sheet names and file names
too.

Okay, one more bug workaround I'm dying to share. When pasting Office
tables, charts, graphics, etc. as metafiles, one often finds that colored
text, and certain other colored graphical elements, are shown as black in
the
pasted metafile graphic. Fix this problem by pretending to print to a
color
printer before doing the cut and paste (go through the print dialogs,
selecting a color printer, then cancel the print dialog).

I've never seen this as I'm always connected to a color printer.

I'm using Excel 2003 SP2, Excel 2002 SP2 or 3 (I forget which), and Excel
2000 SP3. If you think any of these are strange, wait until you try Excel
2007.

- Jon

 

[Robin Hammond]

Jon and Dave,

I'm very intersted in the problem with loss of colours on the paste metafile
to Word issue. In a production/commercial environment, with the "DTP/report
production" machines running XP pro SP2, I am seeing this issue where greys
become blacks, reds become blacks, etc., and it is driving them nuts. In
short, we have working code on some machines (most running SP1) but once it
gets to the people who actually fine tune the word docs for print, it all
goes wrong.

We've been trying to figure out the build differences for a week, and will
try the printer trick tomorrow morning, but if you have any further thoughts
I would very much like to hear them.

 

[Dave M]

The size of axis titles, chart titles, and data labels is not even exposed
to VBA. But there are workarounds.

Sorry, should have been more clear. .Top and .Left are exposed but height
and width must be determined by bumping the PlotArea against the walls and
finding out where its edges end up.

This is true of certain properties of marker-based chart series (i.e., line
and XY, not column, bar, area). Rather than DisplayBlanksAs, which is not a
reliable workaround, temporarily convert the series to area while working on
it, then convert it back.

The DisplayBlanksAs trick has been rock-solid for me. Under what conditions
does it fail?

Not surprising, is it? A completely empty chart has no title.

Actually, it *is* surprising, and it is illogical from a programming
standpoint. It is senseless that a variable's contents (such as the text of
a chart title) can't be accessed during times that the variable is not usable
by some other part of a program (in this case, the chart display machinery).
A completely empty chart may indeed *have* a title when none is displayed.
Try this: create a chart with a title, then delete all series. An attempt
to programmatically read the chart's title will fail, but go ahead and give
the chart a series again, and you will see that chart redrawn with its
original title. The title text was always stored in memory; you just
couldn't get at it when the display machinery had nothing to do.

This argument applies to my "Tip 2" above. MS should really fix this bad
behavior so that programmatic manipulation of charts isn't so hard to do.

You get an error? In my experience (and in the test I just ran) the plot
area moves or resizes only as far as it will go, without an error.

I did the same test in Excel 2002 without error. I guess that bug has been
fixed since 2000 (where I observed the bug.)

Another solution is to use recognized characters in the chart object name.
You need to watch out what characters are used in sheet names and file names
too.

I'm confident that space and hyphen are both "recognized characters",
whatever that means. Both are acceptable in sheet names and filenames. If
neither were acceptable in ChartObject names, then the runtime system would
raise an error (as it does when renaming files or worksheets with names
containing invalid characters). Instead, a ChartObject will happily eat and
regurgitate names with these characters, but the collection starts behaving
as nutty as the list of characters that cause the nuttiness.

I've never seen this as I'm always connected to a color printer.

I'm using Excel 2003 SP2, Excel 2002 SP2 or 3 (I forget which), and Excel
2000 SP3. If you think any of these are strange, wait until you try Excel
2007.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Dave M
Diesel engine developer, lowly Excel user, nose picker, booger flicker

 

[Peter T]

Comments under your 'Tips' 3 & 4 and your 'Todays' bug

Sorry, should have been more clear. .Top and .Left are exposed but height
and width must be determined by bumping the PlotArea against the walls and
finding out where its edges end up.


The DisplayBlanksAs trick has been rock-solid for me. Under what conditions
does it fail?



Actually, it *is* surprising, and it is illogical from a programming
standpoint. It is senseless that a variable's contents (such as the text of
a chart title) can't be accessed during times that the variable is not usable
by some other part of a program (in this case, the chart display machinery).
A completely empty chart may indeed *have* a title when none is displayed.
Try this: create a chart with a title, then delete all series. An attempt
to programmatically read the chart's title will fail, but go ahead and give
the chart a series again, and you will see that chart redrawn with its
original title. The title text was always stored in memory; you just
couldn't get at it when the display machinery had nothing to do.

This argument applies to my "Tip 2" above. MS should really fix this bad
behavior so that programmatic manipulation of charts isn't so hard to do.

It's not only the Chart title that can't be accessed when a chartobject has
no series but also various other things, eg the Legend. These will be
re-applied 'as-was' and readable when a series is re-applied. Not sure why
this is a problem though, if you know there are no series defer accessing
those objects until the chart is known to have at least one series.

I did the same test in Excel 2002 without error. I guess that bug has been
fixed since 2000 (where I observed the bug.)

I can't recreate the problem in xl97 or xl2000, plot resizes without error
as Jon described.

I'm confident that space and hyphen are both "recognized characters",
whatever that means. Both are acceptable in sheet names and filenames. If
neither were acceptable in ChartObject names, then the runtime system would
raise an error (as it does when renaming files or worksheets with names
containing invalid characters). Instead, a ChartObject will happily eat and
regurgitate names with these characters, but the collection starts behaving
as nutty as the list of characters that cause the nuttiness.

This is not a particularly a chartobject issue but applies to drawingobjects
in general. Indeed it is a pain! I think this relates to the fact that
shapes have two names. Workaround might be to loop through shapes. Eg

if shp.type = msoChart then
set objCht = shp.drawingobject

Dave M
Diesel engine developer, lowly Excel user, nose picker, booger flicker

Regards,
Peter T

 

[Robin Hammond]

It turns out an auto_open routine with a switch to of ActivePrinter to the
Microsoft Document Imaging print driver seems to work in getting around
this.

Robin Hammond
www.enhanceddatasystems.com

 

[Jon Peltier]

Dave -

Sorry, should have been more clear. .Top and .Left are exposed but height
and width must be determined by bumping the PlotArea against the walls and
finding out where its edges end up.

Yes, that's one of the workarounds, probably the simplest.

The DisplayBlanksAs trick has been rock-solid for me. Under what
conditions
does it fail?

If the range does not contain blanks, but instead contains other
non-plotting content (text or error besides #N/A), and it's an XY or Line
chart.

Actually, it *is* surprising, and it is illogical from a programming
standpoint. It is senseless that a variable's contents (such as the text
of
a chart title) can't be accessed during times that the variable is not
usable
by some other part of a program (in this case, the chart display
machinery).
A completely empty chart may indeed *have* a title when none is displayed.
Try this: create a chart with a title, then delete all series. An
attempt
to programmatically read the chart's title will fail, but go ahead and
give
the chart a series again, and you will see that chart redrawn with its
original title. The title text was always stored in memory; you just
couldn't get at it when the display machinery had nothing to do.

This argument applies to my "Tip 2" above. MS should really fix this bad
behavior so that programmatic manipulation of charts isn't so hard to do.

Following your demo, I can't get the title text either in VBA or in the UI,
since Chart Options is unavailable without any series in the chart. I
suppose this means VBA mimics the UI.

There are other things inaccessible to VBA as well, and I wish they'd
upgrade VBA to include them, but I've been wishing since Excel 97.

I did the same test in Excel 2002 without error. I guess that bug has
been
fixed since 2000 (where I observed the bug.)

I don't recall this ever being a problem even back in '97. I've been doing
this for a long time, and didn't upgrade past '97 until around 2002 or 3.

I'm confident that space and hyphen are both "recognized characters",
whatever that means. Both are acceptable in sheet names and filenames.
If
neither were acceptable in ChartObject names, then the runtime system
would
raise an error (as it does when renaming files or worksheets with names
containing invalid characters). Instead, a ChartObject will happily eat
and
regurgitate names with these characters, but the collection starts
behaving
as nutty as the list of characters that cause the nuttiness.

I'm curious, does this problem occur with other shapes (besides chart
objects) as well?

- Jon

 

[Jon Peltier]

Tip 3:

It's not only the Chart title that can't be accessed when a chartobject
has
no series but also various other things, eg the Legend. These will be
re-applied 'as-was' and readable when a series is re-applied. Not sure why
this is a problem though, if you know there are no series defer accessing
those objects until the chart is known to have at least one series.

A point I neglected to make.

I can't recreate the problem in xl97 or xl2000, plot resizes without error
as Jon described.

Good, so I'm not crazy.

This is not a particularly a chartobject issue but applies to
drawingobjects
in general. Indeed it is a pain! I think this relates to the fact that
shapes have two names. Workaround might be to loop through shapes. Eg

Answers my question

if shp.type = msoChart then
set objCht = shp.drawingobject

Actually Dave's workaround seems to work:

For Each ChtOb In ActiveSheet.ChartObjects
If ChtOb.Name = strMyNameWithDisallowedCharacters Then
Set chtobTheOneIWant = ChtOb
End If
Next

- Jon

 

[Peter T]

And now, today's bug [names with punctuation]

I'm curious, does this problem occur with other shapes (besides chart
objects) as well?

- Jon
-------

Indeed it does as I mentioned in my previous post, names with punctuation
can be accessed as Shape but not as Some-type-of-DrawingObject. A Space in
the name isn't a problem but a dot is.

It's hit me and prompted one of just a handful of questions I've raised in
this ng, see the last paragraph in this -
http://tinyurl.com/ycv4ae

Regards,
Peter T

 

[Peter T]

Sorry Jon, I hadn't related your "Answers my question" in your adjacent post
to this below. I wasn't trying to force the point!

Regards,
Peter T

And now, today's bug [names with punctuation]

I'm curious, does this problem occur with other shapes (besides chart
objects) as well?

- Jon
-------

Indeed it does as I mentioned in my previous post, names with punctuation
can be accessed as Shape but not as Some-type-of-DrawingObject. A Space in
the name isn't a problem but a dot is.

It's hit me and prompted one of just a handful of questions I've raised in
this ng, see the last paragraph in this -
http://tinyurl.com/ycv4ae

Regards,
Peter T

 

[Dave M]

I'm curious, does this problem occur with other shapes (besides chart
objects) as well?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

On Friday I experimented with a rectangle and to see how Shape.Name would
handle things. No problem. Shape.DrawingObject.Name: no problem (side
note: where the hell does DrawingObject fall in the hierarchy of things? I
can't find it docuimented anywhere.)

For laffs, here is how ChartObject reacts to each character. What does
*that* pattern mean?

dec. char. bonkers
-----------------------------------
32 Yes
33 ! Yes
34 " Yes
35 # Yes
36 $ Yes
37 % Yes
38 & Yes
39 Yes
40 ( Yes
41 ) Yes
42 * Yes
43 + Yes
44 , Yes
45 - Yes
46 . Yes
47 / Yes
48 0 No
49 1 No
50 2 No
51 3 No
52 4 No
53 5 No
54 6 No
55 7 No
56 8 No
57 9 No
58 : Yes
59 ; Yes
60 < Yes
61 = Yes
62 > Yes
63 ? Yes
64 @ Yes
65 A No
66 B No
67 C No
68 D No
69 E No
70 F No
71 G No
72 H No
73 I No
74 J No
75 K No
76 L No
77 M No
78 N No
79 O No
80 P No
81 Q No
82 R No
83 S No
84 T No
85 U No
86 V No
87 W No
88 X No
89 Y No
90 Z No
91 [ Yes
92 \ Yes
93 ] Yes
94 ^ Yes
95 _ No
96 ` Yes
97 a No
98 b No
99 c No
100 d No
101 e No
102 f No
103 g No
104 h No
105 i No
106 j No
107 k No
108 l No
109 m No
110 n No
111 o No
112 p No
113 q No
114 r No
115 s No
116 t No
117 u No
118 v No
119 w No
120 x No
121 y No
122 z No
123 { Yes
124 | Yes
125 } Yes
126 ~ Yes
127  Yes
128 € Yes
129 Â Yes
130 ‚ Yes
131 Æ’ No
132 „ Yes
133 … Yes
134 † Yes
135 ‡ Yes
136 ˆ Yes
137 ‰ Yes
138 Å  No
139 ‹ Yes
140 Å’ No
141 Â Yes
142 Ž No
143 Â Yes
144 Â Yes
145 ‘ Yes
146 ’ Yes
147 “ Yes
148 †Yes
149 • Yes
150 – Yes
151 — Yes
152 ˜ Yes
153 â„¢ Yes
154 Å¡ No
155 › Yes
156 Å“ No
157 Â Yes
158 ž No
159 Ÿ No
160 Yes
161 ¡ Yes
162 ¢ Yes
163 £ Yes
164 ¤ Yes
165 ¥ Yes
166 ¦ Yes
167 § Yes
168 ¨ Yes
169 © Yes
170 ª No
171 « Yes
172 ¬ Yes
173 ­ Yes
174 ® Yes
175 ¯ Yes
176 ° Yes
177 ± Yes
178 ² Yes
179 ³ Yes
180 ´ Yes
181 µ No
182 ¶ Yes
183 · Yes
184 ¸ Yes
185 ¹ Yes
186 º No
187 » Yes
188 ¼ Yes
189 ½ Yes
190 ¾ Yes
191 ¿ Yes
192 À No
193 Ã No
194 Â No
195 Ã No
196 Ä No
197 Ã… No
198 Æ No
199 Ç No
200 È No
201 É No
202 Ê No
203 Ë No
204 Ì No
205 Ã No
206 ÃŽ No
207 Ã No
208 Ã No
209 Ñ No
210 Ã’ No
211 Ó No
212 Ô No
213 Õ No
214 Ö No
215 × Yes
216 Ø No
217 Ù No
218 Ú No
219 Û No
220 Ü No
221 Ã No
222 Þ No
223 ß No
224 à No
225 á No
226 â No
227 ã No
228 ä No
229 å No
230 æ No
231 ç No
232 è No
233 é No
234 ê No
235 ë No
236 ì No
237 í No
238 î No
239 ï No
240 ð No
241 ñ No
242 ò No
243 ó No
244 ô No
245 õ No
246 ö No
247 ÷ Yes
248 ø No
249 ù No
250 ú No
251 û No
252 ü No
253 ý No
254 þ No
255 ÿ No

 

[Peter T]

On Friday I experimented with a rectangle and to see how Shape.Name would

handle things. No problem. Shape.DrawingObject.Name: no problem (side
note: where the hell does DrawingObject fall in the hierarchy of things? I
can't find it docuimented anywhere.)

a few general comments here, far from exhaustive
http://tinyurl.com/y52q24

For laffs, here is how ChartObject reacts to each character. What does
*that* pattern mean?

< results snipped >

I replicated most of your results for names with characters not recognized
at the drawingobject level.
For me a Space in the middle of the name is not a problem though at either
end it is. Also a few more characters in the range 128-255 didn't work for
me as it seems they did for you.

In testing came accross something else. Cannot prefix the name with a number
(possibly multi-digit) that's the same as the 'object-id' of some other
existing shape. The 'object-id' is a counter that's incremented whenever an
object is added to the sheet, the default name's suffix. Can only be reset
if all shapes on the sheet are deleted and file saved before adding more
shapes.

Regards,
Peter T

 

[Jon Peltier]

(side note: where the hell does DrawingObject fall in the hierarchy of
things? I
can't find it docuimented anywhere.)

DrawingObjects is an older class of objects, probably related to the version
of VBA in Excel 5/95. They are listed in the Object Browser if you right
click and select Show Hidden Members. Ironically, some things work better in
VBA using DrawingObjects than Shapes, and some things come out of the macro
recorder as DrawingObjects, not Shapes. Having fun yet?

- Jon