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

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.

"Dave M" wrote:

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

"Dave M" <Dave (E-Mail Removed)> wrote in message
news:794BF736-6B3B-4D5A-9ACF-(E-Mail Removed)...
> 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

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.

--
Robin Hammond
www.enhanceddatasystems.com


"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "Dave M" <Dave (E-Mail Removed)> wrote in message
> news:794BF736-6B3B-4D5A-9ACF-(E-Mail Removed)...
>> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>

"Jon Peltier" wrote:

>
> "Dave M" <Dave (E-Mail Removed)> wrote in message
> news:794BF736-6B3B-4D5A-9ACF-(E-Mail Removed)...
> > 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

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

"Dave M" <(E-Mail Removed)> wrote in message
news:<3E1C2665-9F86-48A6-8087-(E-Mail Removed)>...
>
>
> "Jon Peltier" wrote:
>
> >
> > "Dave M" <Dave (E-Mail Removed)> wrote in message
> > news:794BF736-6B3B-4D5A-9ACF-(E-Mail Removed)...
> > > 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.

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.

> >
> > > 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 can't recreate the problem in xl97 or xl2000, plot resizes without error
as Jon described.

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

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

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

Regards,
Peter T

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


"Robin Hammond" <(E-Mail Removed)> wrote in
message news:456046ac$1@127.0.0.1...
> 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.
>
> --
> Robin Hammond
> www.enhanceddatasystems.com
>
>
> "Jon Peltier" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> "Dave M" <Dave (E-Mail Removed)> wrote in message
>> news:794BF736-6B3B-4D5A-9ACF-(E-Mail Removed)...
>>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>
>

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
_______

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

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

Good, so I'm not crazy.

>> > > And now, today's bug.
> 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> >> > And now, today's bug [names with punctuation]
> > 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
> -------

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