"Jon Peltier" wrote:
"Dave M" <Dave wrote in message
...
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.
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.
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.
The DisplayBlanksAs trick has been rock-solid for me. Under what conditions
does it fail?
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.
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.
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.
I did the same test in Excel 2002 without error. I guess that bug has been
fixed since 2000 (where I observed the bug.)
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.
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.
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Dave M
Diesel engine developer, lowly Excel user, nose picker, booger flicker