If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Bug with Chart.Name?
Aha! Gotcha!
If so it's only perhaps if I didn't clearly explain g Sometimes a For Each loop doesn't find each item in a collection. Absolutely, and this "Gotcha" is the very point I have been trying to make to the OP. For Each Drawingobject-type (eg ChartObject, Rectangle, etc) can do incorrect things and hence several times in this thread (and over the years in several other posts) I have been trying to ensure the OP loops For To Next, ie by index. (I gave an example in an earlier post). However, For Each shp in .Shapes is generally reliable (even so with one or two caveats), and hence what I meant by a For To index (drawingobject level) loop should "normally" return same as a For Each shape. Unfortunately, looping large numbers of shapes is very considerably slower than the equivalent drawingobject level, either For Each or by index method. Think I've mentioned already, but a "For Each Drawingobject-type" can confusingly fail with duplicate names; the first name in the collection processes correctly but the second (& subsequent) found duplicate name in the loop ends up re-referencing (and re-processing) the first duplicate name, if that makes sense. Further, For Each Drawingobject can entirely fail to reference any names with punctuation, eg "MyChart.1". There are one or two other reasons this type of loop can fail. For each Drawingobject can also fail in the opposite way and pick up "un-wanted" objects such as a chartobjects which are grouped* - very confusing! There's more! I think sheets or worksheets are as well Fortunately I'm pretty confident there's no potential For Each problem with sheets, but there are different kinds of problems with other non drawingobjects, eg Series (in rare scenarios). Regards, Peter PS * eg, rename a chartobject, copy it, group the pair, loop For Each chartobject "Jon Peltier" wrote in message ... I would not expect this to work differently to a For i = 1 to .Chartobjects.Count loop Aha! Gotcha! Sometimes a For Each loop doesn't find each item in a collection. It wasn't until I read this response that I remembered this obscure little feature. Chart Objects are one of the types of items that can be missed. I'm not sure, but I think sheets or worksheets are as well. It hasn't happened to me frequently, but lately I've trained myself to use a looping counter variable in my For Next loops. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Peter T" peter_t@discussions wrote in message ... I did try the normal loop based on max chart count and the results were the same. Sorry, I just didn't post it since there was no new data. I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way to loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to .Chartobjects.Count loop From what you have described about chartobjects having been renamed and copied I'm not surprised your For Each loop has failed to reference all correctly. I know it might seem odd but actually, as your subject line suggests, there can be a bug of sorts when looping For Each with any object type at the 'DrawingObject' level; of which ChartObject is a sub type. Apart from[color=blue][i][color=green][i] picking up the wrong object, in the case of duplicate names, names that include punctuation can be missed completely. Remember, we are talking about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be the same). Regards, Peter T "bart13" wrote in message ... It was meaningful to me in the sense that it displayed exactly what I was expecting. Charts are just plain being missed in the loop, no matter how the loop is constructed or even if the if statement is removed. The ChartObjects are getting corrupted when renamed, and its pretty consistent. I did try the normal loop based on max chart count and the results were the same. Sorry, I just didn't post it since there was no new data. Thanks for clarifying on the grouping. I've seen it on one of the properties menus but never took it any further since I seldom use any extra chart elements like text boxes, etc. I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end. It seems that every time I copy a chart now and then rename it, the ChartObject gets corrupted and will never again be seen by any loop. If I don't rename it and just translate the "Chart ###" name to my own filename in a select case statement, all is well. I've even tried deleting about 30 of the extra charts in the main chart tab on the theory that there's some unknown limit I've exceeded, but it made no difference. bart13 Peter T;677841 Wrote: Afraid it's difficult to know how to unwrap those debug lines and make any sense of them. I was hoping though that the information might be meaningful to you, in particular indicate if any charts were not being picked up as expected, or a chart being picked up twice (strangely that's possible in a For Each loop). Did you try looping with the For...To...Next method and compare the debug, as I suggested. I forgot to comment on this from your earlier post --- I haven't done any grouping that I know of and frankly I'm not even sure what it means in this context.--- You can group chartobjects with any other objects, eg rectangle or other chartobjects, by selecting them, right-click then group. Copying then ungrouping can lead to duplicate names (particularly objects that had been renamed from their given default). Regards, Peter T "bart13" wrote in message ...- All the charts in the test tab with only the 6 charts are not being processed as you can see in the debug output, and just as the text file output also shows. Only three of the six are being seen, and in the first debug section below the names are duplicated. 0 dow_gold_oil_crb1900current_rev Pub2 dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1900current_rev.png 0 dow_gold_oil_crb1800current_rev Pub2 dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1800current_rev.png 0 dow_gold_oil_crb1800current Pub2 dow_gold_oil_crb1800current C:\gk\temp\Pub2 dow_gold_oil_crb1800current.png 0 dow_gold_oil_crb1900current_rev Pub2 dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1900current_rev.png 0 dow_gold_oil_crb1800current_rev Pub2 dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1800current_rev.png 0 dow_gold_oil_crb1800current Pub2 dow_gold_oil_crb1800current C:\gk\temp\Pub2 dow_gold_oil_crb1800current.png Here's the same output but with the if/end if commented out and of course dropping the elseif condition: 1 dow_gold_oil_crb1900current_rev Pub2 dow_gold_oil_crb1900current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1900current_rev.png 2 dow_gold_oil_crb1800current_rev Pub2 dow_gold_oil_crb1800current_rev C:\gk\temp\Pub2 dow_gold_oil_crb1800current_rev.png 3 dow_gold_oil_crb1800current Pub2 dow_gold_oil_crb1800current C:\gk\temp\Pub2 dow_gold_oil_crb1800current.png The three charts that are being missed aren't anywhere in either output and ActiveSheet.ChartObjects.Count does return 6. I have confirmed again that all six charts do have unique and different names in the name box. Although it may not help, the main chart tab has 350 charts but only 323 are actually processed, whether the Chart/Chert statement is there or not. Also, the entire workbook is gigantic - over 500MB. The purpose of the if condition is so that all I have to do to save a new chart for the web site is to change the chart name away from the default. Its good to have it confirmed about ChartObject and Chart names. By the way, thanks for the help - much appreciated. I've been coding since 1980 but this is my first VBA macro. bart13 Peter T;677602 Wrote: Are all your charts being processed or are some skipped due to this line- If Left(strChartName, 5) "Chert" Then-- (Chert/Chart noted) comment the export line and just before the "End If" and add these lines debug.print counter, Pict.Name, Pict.chart.name, SaveName Else debug.print "Else " & Pict.Name, Pict.chart.name End if You could also try looping For..To..Next and compare the debug lines. Comment the For Each line (also again with the export line commented and the same debug lines) For i = 1 to Activesheet.Chartobjects.Count Set Pict = ActiveSheet.ChartObjects(i) etc Hopefully comparing debug after looping both ways will indicate what's going on (even if the debug sets are the same). Btw why the If condition? Also the debug lines should also highlight what Jon mentioned about ChartObject and Chart names not being the same. I'm sure you know but just in case, press ctrl-g to view the debug lines in the Immediate window. Regards, Peter T ... -- bart13 |
#12
|
|||
|
|||
Bug with Chart.Name?
Yes - very much so. The straight loop (as in "for i = 1 to activesheet.chartobjects.count" ) has wrong results, just like every other attempt and regardless of whether the if test was being used or not. There are 6 charts on the tab, only three show up in both the actual text file and the debug print statement. No matter what the code is, charts are being skipped. To state it another way, Pict.Name and Pict.Chart.Name return the exact same matching values - every time. But both only return three names and there are six charts, and all six have unique names in the name box. No matter if I look at the output in the text file or from the debug print statement, its the same issue. Six charts in the tab, only three recognized. And I do betray my ancient status as a coder. The last time I did any Basic coding, there was no such thing as a for each loop so the "for i = 1 to ..." is normal to me. I did double check the name box again for all six charts, and all the names are both unique and do not contain punctuation either (unless an underscore is considered punctuation). I'm obviously missing something since it appears you're saying I have dupe names, but I have no way of which I'm aware to change the duplicate ones nor is there any code that I can run which will distinguish the dupes. The only way that works on new copied charts is not to change the name but rather hard code it into a select case set of statements based on the "Chart ###" name. Are you saying that this is expected behavior and an open and old bug (or something similar), and that I should just continue to do the hard coding and work around? I'm not trying to be accusatory, just in case. Regards, bart Peter T;678438 Wrote: [color=blue][i] I did try the normal loop based on max chart count and the results were- the same. Sorry, I just didn't post it since there was no new data.- I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way to loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to ..Chartobjects.Count loop From what you have described about chartobjects having been renamed and copied I'm not surprised your For Each loop has failed to reference all correctly. I know it might seem odd but actually, as your subject line suggests, there can be a bug of sorts when looping For Each with any object type at the 'DrawingObject' level; of which ChartObject is a sub type. Apart from picking up the wrong object, in the case of duplicate names, names that include punctuation can be missed completely. Remember, we are talking about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be the same). Regards, Peter T "bart13" wrote in message ... It was meaningful to me in the sense that it displayed exactly what I was expecting. Charts are just plain being missed in the loop, no matter how the loop is constructed or even if the if statement is removed. The ChartObjects are getting corrupted when renamed, and its pretty consistent. I did try the normal loop based on max chart count and the results were the same. Sorry, I just didn't post it since there was no new data. Thanks for clarifying on the grouping. I've seen it on one of the properties menus but never took it any further since I seldom use any extra chart elements like text boxes, etc. I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end. It seems that every time I copy a chart now and then rename it, the ChartObject gets corrupted and will never again be seen by any loop. If I don't rename it and just translate the "Chart ###" name to my own filename in a select case statement, all is well. I've even tried deleting about 30 of the extra charts in the main chart tab on the theory that there's some unknown limit I've exceeded, but it made no difference. bart13 -- bart13 |
#13
|
|||
|
|||
Bug with Chart.Name?
I really would expect both the "for i = 1 to .chartobjects.count" and the
For each shp in .Shapes loop (with if msoChart test) to work correctly. Possibly there's something else in your own code at fault, or some other bug that we have all missed. If you like you can send me a zipped stripped down non-sensitive version of what you have. Make a copy of your book, prune as much as possible in terms of data, charts, sheets and not least code; leaving just enough to demonstrate the problem. Regards, Peter T pmbthornton gmail com "bart13" wrote in message ...[color=blue][i] Yes - very much so. The straight loop (as in "for i = 1 to activesheet.chartobjects.count" ) has wrong results, just like every other attempt and regardless of whether the if test was being used or not. There are 6 charts on the tab, only three show up in both the actual text file and the debug print statement. No matter what the code is, charts are being skipped. To state it another way, Pict.Name and Pict.Chart.Name return the exact same matching values - every time. But both only return three names and there are six charts, and all six have unique names in the name box. No matter if I look at the output in the text file or from the debug print statement, its the same issue. Six charts in the tab, only three recognized. And I do betray my ancient status as a coder. The last time I did any Basic coding, there was no such thing as a for each loop so the "for i = 1 to ..." is normal to me. I did double check the name box again for all six charts, and all the names are both unique and do not contain punctuation either (unless an underscore is considered punctuation). I'm obviously missing something since it appears you're saying I have dupe names, but I have no way of which I'm aware to change the duplicate ones nor is there any code that I can run which will distinguish the dupes. The only way that works on new copied charts is not to change the name but rather hard code it into a select case set of statements based on the "Chart ###" name. Are you saying that this is expected behavior and an open and old bug (or something similar), and that I should just continue to do the hard coding and work around? I'm not trying to be accusatory, just in case. Regards, bart Peter T;678438 Wrote: I did try the normal loop based on max chart count and the results were- the same. Sorry, I just didn't post it since there was no new data.- I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way to loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to ..Chartobjects.Count loop From what you have described about chartobjects having been renamed and copied I'm not surprised your For Each loop has failed to reference all correctly. I know it might seem odd but actually, as your subject line suggests, there can be a bug of sorts when looping For Each with any object type at the 'DrawingObject' level; of which ChartObject is a sub type. Apart from picking up the wrong object, in the case of duplicate names, names that include punctuation can be missed completely. Remember, we are talking about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be the same). Regards, Peter T "bart13" wrote in message ... It was meaningful to me in the sense that it displayed exactly what I was expecting. Charts are just plain being missed in the loop, no matter how the loop is constructed or even if the if statement is removed. The ChartObjects are getting corrupted when renamed, and its pretty consistent. I did try the normal loop based on max chart count and the results were the same. Sorry, I just didn't post it since there was no new data. Thanks for clarifying on the grouping. I've seen it on one of the properties menus but never took it any further since I seldom use any extra chart elements like text boxes, etc. I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end. It seems that every time I copy a chart now and then rename it, the ChartObject gets corrupted and will never again be seen by any loop. If I don't rename it and just translate the "Chart ###" name to my own filename in a select case statement, all is well. I've even tried deleting about 30 of the extra charts in the main chart tab on the theory that there's some unknown limit I've exceeded, but it made no difference. bart13 -- bart13 |
#14
|
|||
|
|||
Bug with Chart.Name?
The email with a link to the workbook will be on the way soon. The week was too wild to get it to you sooner. I did not try the .Shapes loop yet, but don't have much hope for it. Thanks, bart13 Peter T;678958 Wrote: [color=blue][i] I really would expect both the "for i = 1 to .chartobjects.count" and the For each shp in .Shapes loop (with if msoChart test) to work correctly. Possibly there's something else in your own code at fault, or some other bug that we have all missed. If you like you can send me a zipped stripped down non-sensitive version of what you have. Make a copy of your book, prune as much as possible in terms of data, charts, sheets and not least code; leaving just enough to demonstrate the problem. Regards, Peter T pmbthornton gmail com "bart13" wrote in message ...- Yes - very much so. The straight loop (as in "for i = 1 to activesheet.chartobjects.count" ) has wrong results, just like every other attempt and regardless of whether the if test was being used or not. There are 6 charts on the tab, only three show up in both the actual text file and the debug print statement. No matter what the code is, charts are being skipped. To state it another way, Pict.Name and Pict.Chart.Name return the exact same matching values - every time. But both only return three names and there are six charts, and all six have unique names in the name box. No matter if I look at the output in the text file or from the debug print statement, its the same issue. Six charts in the tab, only three recognized. And I do betray my ancient status as a coder. The last time I did any Basic coding, there was no such thing as a for each loop so the "for i = 1 to ..." is normal to me. I did double check the name box again for all six charts, and all the names are both unique and do not contain punctuation either (unless an underscore is considered punctuation). I'm obviously missing something since it appears you're saying I have dupe names, but I have no way of which I'm aware to change the duplicate ones nor is there any code that I can run which will distinguish the dupes. The only way that works on new copied charts is not to change the name but rather hard code it into a select case set of statements based on the "Chart ###" name. Are you saying that this is expected behavior and an open and old bug (or something similar), and that I should just continue to do the hard coding and work around? I'm not trying to be accusatory, just in case. Regards, bart Peter T;678438 Wrote:-- I did try the normal loop based on max chart count and the results- were-- the same. Sorry, I just didn't post it since there was no new data.-- I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way to loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to ..Chartobjects.Count loop From what you have described about chartobjects having been renamed and copied I'm not surprised your For Each loop has failed to reference all correctly. I know it might seem odd but actually, as your subject line suggests, there can be a bug of sorts when looping For Each with any object type at the 'DrawingObject' level; of which ChartObject is a sub type. Apart from picking up the wrong object, in the case of duplicate names, names that include punctuation can be missed completely. Remember, we are talking about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be the same). Regards, Peter T "bart13" wrote in message ...- It was meaningful to me in the sense that it displayed exactly what I was expecting. Charts are just plain being missed in the loop, no matter how the loop is constructed or even if the if statement is removed. The ChartObjects are getting corrupted when renamed, and its pretty consistent. I did try the normal loop based on max chart count and the results- were- the same. Sorry, I just didn't post it since there was no new data. Thanks for clarifying on the grouping. I've seen it on one of the properties menus but never took it any further since I seldom use any extra chart elements like text boxes, etc. I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end. It seems that every time I copy a chart now and then rename it, the ChartObject gets corrupted and will never again be seen by any loop.- If- I don't rename it and just translate the "Chart ###" name to my own filename in a select case statement, all is well. I've even tried deleting about 30 of the extra charts in the main- chart- tab on the theory that there's some unknown limit I've exceeded, but- it- made no difference. bart13 - - -- bart13- -- bart13 |
#15
|
|||
|
|||
Bug with Chart.Name?
OK I got it, the stripped down version of only 14Mg !
The problem was easy to find, it was due to exactly one of the potential reasons I predicted when looping For Each ChartObject (or any drawing object type), specifically in a reply to you " names that include punctuation can be missed completely. " and in my reply to Jon " Further, For Each Drawingobject can entirely fail to reference any names with punctuation, eg "MyChart.1". " You have three chartobject names like this "dow_gold_oil_crb1900-current" note the - dash ! Solution is either rename those objects or change your loop For Each Chartobject loop to either of the other loop methods I've suggested many times in this thread. For i = 1 to ws.ChartObjects.Count Set chtObj = ws.ChartObjects(i) or For Each shp in ws.Shapes If shp.Type = msoChart then 'etc I'd use the index method. BTW, in your file I see you tried in a test macro to implement an index style loop. Unfortunately you did it like this For Counter = 1 to ws.ChartObjects.Count ' do stuff Counter = Counter + 1 Next So your loop would only process every other chart ! Regards, Peter T "bart13" wrote in message ...[color=blue][i] The email with a link to the workbook will be on the way soon. The week was too wild to get it to you sooner. I did not try the .Shapes loop yet, but don't have much hope for it. Thanks, bart13 Peter T;678958 Wrote: I really would expect both the "for i = 1 to .chartobjects.count" and the For each shp in .Shapes loop (with if msoChart test) to work correctly. Possibly there's something else in your own code at fault, or some other bug that we have all missed. If you like you can send me a zipped stripped down non-sensitive version of what you have. Make a copy of your book, prune as much as possible in terms of data, charts, sheets and not least code; leaving just enough to demonstrate the problem. Regards, Peter T pmbthornton gmail com "bart13" wrote in message ...- Yes - very much so. The straight loop (as in "for i = 1 to activesheet.chartobjects.count" ) has wrong results, just like every other attempt and regardless of whether the if test was being used or not. There are 6 charts on the tab, only three show up in both the actual text file and the debug print statement. No matter what the code is, charts are being skipped. To state it another way, Pict.Name and Pict.Chart.Name return the exact same matching values - every time. But both only return three names and there are six charts, and all six have unique names in the name box. No matter if I look at the output in the text file or from the debug print statement, its the same issue. Six charts in the tab, only three recognized. And I do betray my ancient status as a coder. The last time I did any Basic coding, there was no such thing as a for each loop so the "for i = 1 to ..." is normal to me. I did double check the name box again for all six charts, and all the names are both unique and do not contain punctuation either (unless an underscore is considered punctuation). I'm obviously missing something since it appears you're saying I have dupe names, but I have no way of which I'm aware to change the duplicate ones nor is there any code that I can run which will distinguish the dupes. The only way that works on new copied charts is not to change the name but rather hard code it into a select case set of statements based on the "Chart ###" name. Are you saying that this is expected behavior and an open and old bug (or something similar), and that I should just continue to do the hard coding and work around? I'm not trying to be accusatory, just in case. Regards, bart Peter T;678438 Wrote:-- I did try the normal loop based on max chart count and the results- were-- the same. Sorry, I just didn't post it since there was no new data.-- I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way to loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to ..Chartobjects.Count loop From what you have described about chartobjects having been renamed and copied I'm not surprised your For Each loop has failed to reference all correctly. I know it might seem odd but actually, as your subject line suggests, there can be a bug of sorts when looping For Each with any object type at the 'DrawingObject' level; of which ChartObject is a sub type. Apart from picking up the wrong object, in the case of duplicate names, names that include punctuation can be missed completely. Remember, we are talking about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be the same). Regards, Peter T "bart13" wrote in message ...- It was meaningful to me in the sense that it displayed exactly what I was expecting. Charts are just plain being missed in the loop, no matter how the loop is constructed or even if the if statement is removed. The ChartObjects are getting corrupted when renamed, and its pretty consistent. I did try the normal loop based on max chart count and the results- were- the same. Sorry, I just didn't post it since there was no new data. Thanks for clarifying on the grouping. I've seen it on one of the properties menus but never took it any further since I seldom use any extra chart elements like text boxes, etc. I hope Mr. Peltier jumps in with an idea or two. I'm at my wits end. It seems that every time I copy a chart now and then rename it, the ChartObject gets corrupted and will never again be seen by any loop.- If- I don't rename it and just translate the "Chart ###" name to my own filename in a select case statement, all is well. I've even tried deleting about 30 of the extra charts in the main- chart- tab on the theory that there's some unknown limit I've exceeded, but- it- made no difference. bart13 - - -- bart13- -- bart13 |
#16
|
|||
|
|||
Bug with Chart.Name?
I also tried to reply to your off-line email but it bounced back. If you
don't get the second attempt maybe contact me again with a different email address. Regards, Peter T |
#17
|
|||
|
|||
Bug with Chart.Name?
Bingo. Wow - a dash was the basic reason for the skipping! I never would have figured that it was considered as punctuation, especially since an underscore is fine... live & learn as they say. Every O/S and language has quirks and as I've said, this was my first VBA work ever and first Basic coding since the late '80s. Most of my experience since the late '80s and until I retired from Disney in 2004 was as a DBA, sysadmin, *nix script jockey and mainly a Data Architect. Anyhow, I've changed the production code to use a counter based loop and also added a new variable for it. That duplicate use of the Counter variable does not exist in the production code, and was inadvertently not commented out before sending you the file. By the way, the full size version of that workbook is about 210MB zipped, 640MB on disk and well over 1GB in memory. Whenever some extra round tuits come in, I'll be exporting most of the raw data to a database but the site has been growing so fast that the time just hasn't been available. Thanks very much for your patience with me, and the "above & beyond" help. Great board for some of us that don't understand and are unaware of specialized info. Regards, bart13 Peter T;680285 Wrote: [color=blue][i] OK I got it, the stripped down version of only 14Mg ! The problem was easy to find, it was due to exactly one of the potential reasons I predicted when looping For Each ChartObject (or any drawing object type), specifically in a reply to you " names that include punctuation can be missed completely. " and in my reply to Jon " Further, For Each Drawingobject can entirely fail to reference any names with punctuation, eg "MyChart.1". " You have three chartobject names like this "dow_gold_oil_crb1900-current" note the - dash ! Solution is either rename those objects or change your loop For Each Chartobject loop to either of the other loop methods I've suggested many times in this thread. For i = 1 to ws.ChartObjects.Count Set chtObj = ws.ChartObjects(i) or For Each shp in ws.Shapes If shp.Type = msoChart then 'etc I'd use the index method. BTW, in your file I see you tried in a test macro to implement an index style loop. Unfortunately you did it like this For Counter = 1 to ws.ChartObjects.Count ' do stuff Counter = Counter + 1 Next So your loop would only process every other chart ! Regards, Peter T "bart13" wrote in message ...- The email with a link to the workbook will be on the way soon. The week[color=green][i] was too wild to get it to you sooner. I did not try the .Shapes loop yet, but don't have much hope for it. Thanks, bart13 Peter T;678958 Wrote: I really would expect both the "for i = 1 to .chartobjects.count" and the For each shp in .Shapes loop (with if msoChart test) to work correctly. Possibly there's something else in your own code at fault, or some other bug that we have all missed. If you like you can send me a zipped stripped down non-sensitive version of what you have. Make a copy of your book, prune as much as possible in[color=green][i] terms of data, charts, sheets and not least code; leaving just enough to demonstrate the problem. Regards, Peter T pmbthornton gmail com "bart13" wrote in message ...- Yes - very much so. The straight loop (as in "for i = 1 to activesheet.chartobjects.count" ) has wrong results, just like- every- other attempt and regardless of whether the if test was being used- or- not. There are 6 charts on the tab, only three show up in both the actual text file and the debug print statement. No matter what the code is, charts are being skipped. To state it another way, Pict.Name and Pict.Chart.Name return the- exact- same matching values - every time. But both only return three names- and- there are six charts, and all six have unique names in the name box. No matter if I look at the output in the text file or from the debug print statement, its the same issue. Six charts in the tab, only- three- recognized. And I do betray my ancient status as a coder. The last time I did- any- Basic coding, there was no such thing as a for each loop so the "for- i- = 1 to ..." is normal to me. I did double check the name box again for all six charts, and all- the- names are both unique and do not contain punctuation either (unless- an- underscore is considered punctuation). I'm obviously missing something since it appears you're saying I- have- dupe names, but I have no way of which I'm aware to change the duplicate ones nor is there any code that I can run which will distinguish the dupes. The only way that works on new copied charts is not to change the- name- but rather hard code it into a select case set of statements based- on- the "Chart ###" name. Are you saying that this is expected behavior and an open and old- bug- (or something similar), and that I should just continue to do the- hard- coding and work around? I'm not trying to be accusatory, just in- case.- Regards, bart Peter T;678438 Wrote:-- I did try the normal loop based on max chart count and the- results-- were-- the same. Sorry, I just didn't post it since there was no new- data.--- I assume by "the normal loop" you mean for i = 1 to activesheet.chartobjects.count although most people might consider "For..Each" as the "normal" way- to- loop objects. Are you absolutely sure the dubug results are different with the different methods of looping. Here's yet another way you can loop your charts Sub test() Dim shp As Shape, chtObj As ChartObject, cht As Chart For Each shp In ActiveSheet.Shapes If shp.Type = msoChart Then i = i + 1 Set chtObj = shp.DrawingObject Set cht = chtObj.Chart Debug.Print i, chtObj.Name, cht.Name End If Next End Sub I would not expect this to work differently to a For i = 1 to ..Chartobjects.Count loop From what you have described about chartobjects having been renamed- and- copied I'm not surprised your For Each loop has failed to reference- all- correctly. I know it might seem odd but actually, as your subject line- suggests,- there can be a bug of sorts when looping For Each with any object type at- the- 'DrawingObject' level; of which ChartObject is a sub type. Apart- from- picking up the wrong object, in the case of duplicate names, names- that- include punctuation can be missed completely. Remember, we are- talking- about ChartObject name here, which is not (normally) same as chartobject.Chart.Name (though chartObject & Shape names will be- the same). Regards, Peter T -- bart13 |
|
Thread Tools | |
Display Modes | |
|
|