Re: Charts are hard / found a BUG in ChartObject behavior

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dave -

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.

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

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?

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.

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.

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.

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.)

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.

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.

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
_______



.

Relevant Pages

  • Re: Excel VBA Vs Chart Data Tables
    ... efficient using cells. ... there is a limit to how many characters can ... characters (maximum 255 series in a chart), and the Series Name argument is ... Worksheet space is cheap. ...
    (microsoft.public.excel.programming)
  • Re: Chart Y-axis definition. Limitation for Maximum characters ?
    ... and plot order) are only allotted something less than 256 characters (a ... to the scattered cells in the worksheet. ... > the worksheet was also coming in the chart defining range. ... > data points it wasnt specifying those 8 properly. ...
    (microsoft.public.excel.misc)
  • Re: Zero terminated strings
    ... Do you know if the standard mandates that ... Must std::string be able to cope with embedded NULLs? ... null characters, leading to the conclusion that they must be treated ... have to look for statements referring to charT(). ...
    (comp.lang.c)
  • Re: Charts are hard / found a BUG in ChartObject behavior
    ... The size of axis titles, chart titles, and data labels is not even exposed ... The DisplayBlanksAs trick has been rock-solid for me. ... I did the same test in Excel 2002 without error. ... Another solution is to use recognized characters in the chart object name. ...
    (microsoft.public.excel.programming)
  • RE: Extra Lines Inserted in Multi-Line Chart Titles
    ... Very frequently, when I create a chart in Excel 2007, give the chart a ... re-open the file in Excel 2007, I will find that Excel has inserted two extra ... truncates the last character or last few characters in the chart title. ... spaces) and then inserted two more linefeed characters before the next line ...
    (microsoft.public.excel.charting)