|
Charts are hard / found a BUG in ChartObject behavior
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 |
Charts are hard / found a BUG in ChartObject behavior
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 .... |
Charts are hard / found a BUG in ChartObject behavior
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" wrote in message ... "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. 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 _______ |
Charts are hard / found a BUG in ChartObject behavior
"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 |
Charts are hard / found a BUG in ChartObject behavior
Comments under your 'Tips' 3 & 4 and your 'Todays' bug
"Dave M" wrote in message ... "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. 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 |
Charts are hard / found a BUG in ChartObject behavior
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" wrote in message ... 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" wrote in message ... "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. 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 _______ |
Charts are hard / found a BUG in ChartObject behavior
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 _______ |
Charts are hard / found a BUG in ChartObject behavior
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 _______ |
Charts are hard / found a BUG in ChartObject behavior
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 |
Charts are hard / found a BUG in ChartObject behavior
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 "Peter T" <peter_t@discussions wrote in message ... And now, today's bug [names with punctuation] <snip 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 |
Charts are hard / found a BUG in ChartObject behavior
"Jon Peltier" wrote:
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 |
Charts are hard / found a BUG in ChartObject behavior
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 |
Charts are hard / found a BUG in ChartObject behavior
"Dave M" wrote in message ... (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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
| All times are GMT +1. The time now is 05:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com