Bug with Chart.Name?

[bart13]

Excel 2003 with hundreds of charts in 3 tabs and just under 200 tota
tabs. I'm trying to automate the export of them with a macro. The cod
seems fine, but all the charts are not being exported.

I've changed the chart names away from the default Chart ### via th
shift click method but even though the name shows correctly in the nam
box at the upper left, a few are still named Chart ### when exported.

I've even tried copying a few of the charts to a brand new chart tab
no joy. There are six charts (and that's wha
ActiveSheet.ChartObjects.Count shows) in the new tab but only thre
will export. The other three that will not export were created vi
ctrl-c copies of one of the working ones and then changes were mad
through the normal UI to the date range, Chart.Name, etc.

Maybe some kind of weird corruption in the various problem charts o
some unknown limit to Chart.Name... or just plain PEBKAC

 

[Peter T]

Could you explain what you mean by "exported". With the chart Export method
you need to supply a file name for what will be an image of the chart saved
to disc (unique to avoid over writing). This name is not related to the
chart name, even though in practice you might use a similar name, eg
"C:\ChartSales01.gif"

Do you mean perhaps copying to some other workbook. Either way, there can be
problems referencing all chartobjects under relatively rare scenarios (or
any objects at the drawing object level). The following may reference
different charts,

for each chtObj in ws.chartobjects
vs
for i = 1 to ws.ChartObjects.Count
Set chtObj = ws.Chartobjects(i)

(ws is a reference to the sheet)

One way discrepancies can arise is after first renaming, then grouping, then
copying then ungrouping. You can end up with multiple chartobjects having
the same 'new name'.

Normally the For Next loop is more reliable (same as Chartobjects.count) but
the For Each loop will pick up any chartobjects that are still in a group
(but can be problematic for different reasons).

Regards,
Peter T

 

[bart13]

Hi Peter,

I'm doing a loop similar to what you noted, but excluding charts wit
the default name of Chart ###

-
For Each Pict In ActiveSheet.ChartObjects

strChartName = RTrim(Pict.Chart.Name)
tChartName = strChartName
ThisChartNum = Val(Right(strChartName, 4))

testFile.WriteLine strChartName

If Left(strChartName, 5) <> "Chert" Then
Set ThisChart = Pict.Chart
SaveName = SavePath & strChartName & ".png"


ThisChart.Export Filename:=SaveName, FilterName:="PNG"
Counter = Counter + 1

End If

Next
Set ThisChart = Nothing

testFile.Close
-
(note that the actual non test code does have "Chart" instead o
"Chert")


I haven't done any grouping that I know of and frankly I'm not eve
sure what it means in this context.

The truly weird part is that the code usually works and does save th
file just fine. The test tab that I noted has 6 charts in it and thre
of them save just fine, but its like the other three (created by jus
copying one of the working charts, which itself was copied from anothe
chart tab, and then editing the name and other attributes like the
axis via the normal UI ) don't even exist. Their names don't even sho
up in the output text file. All the names are shorter than the max 3
character limit.


bart13

 

[Jon Peltier]

The name of an embedded chart is really the chartobject's name. This is what
you change in the name box after shift-click selecting the chart.

- Jon

 

[Peter T]

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]

All the charts in the test tab with only the 6 charts are not bein
processed as you can see in the debug output, and just as the text fil
output also shows. Only three of the six are being seen, and in th
first debug section below the names are duplicated.

0 dow_gold_oil_crb1900current_rev Pub
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub
dow_gold_oil_crb1900current_rev.pn
0 dow_gold_oil_crb1800current_rev Pub
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub
dow_gold_oil_crb1800current_rev.pn
0 dow_gold_oil_crb1800current Pub
dow_gold_oil_crb1800current C:\gk\temp\Pub
dow_gold_oil_crb1800current.pn
0 dow_gold_oil_crb1900current_rev Pub
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub
dow_gold_oil_crb1900current_rev.pn
0 dow_gold_oil_crb1800current_rev Pub
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub
dow_gold_oil_crb1800current_rev.pn
0 dow_gold_oil_crb1800current Pub
dow_gold_oil_crb1800current C:\gk\temp\Pub
dow_gold_oil_crb1800current.pn

Here's the same output but with the if/end if commented out and o
course dropping the elseif condition

1 dow_gold_oil_crb1900current_rev Pub
dow_gold_oil_crb1900current_rev C:\gk\temp\Pub
dow_gold_oil_crb1900current_rev.pn
2 dow_gold_oil_crb1800current_rev Pub
dow_gold_oil_crb1800current_rev C:\gk\temp\Pub
dow_gold_oil_crb1800current_rev.pn
3 dow_gold_oil_crb1800current Pub
dow_gold_oil_crb1800current C:\gk\temp\Pub
dow_gold_oil_crb1800current.pn

The three charts that are being missed aren't anywhere in either outpu
and ActiveSheet.ChartObjects.Count does return 6. I have confirmed agai
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 onl
323 are actually processed, whether the Chart/Chert statement is ther
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
new chart for the web site is to change the chart name away from th
default

Its good to have it confirmed about ChartObject and Chart names

By the way, thanks for the help - much appreciated. I've been codin
since 1980 but this is my first VBA macro

bart1

 

[Peter T]

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

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]

It was meaningful to me in the sense that it displayed exactly what
was expecting. Charts are just plain being missed in the loop, n
matter how the loop is constructed or even if the if statement i
removed. The ChartObjects are getting corrupted when renamed, and it
pretty consistent.

I did try the normal loop based on max chart count and the results wer
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 th
properties menus but never took it any further since I seldom use an
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, th
ChartObject gets corrupted and will never again be seen by any loop. I
I don't rename it and just translate the "Chart ###" name to my ow
filename in a select case statement, all is well.

I've even tried deleting about 30 of the extra charts in the main char
tab on the theory that there's some unknown limit I've exceeded, but i
made no difference.

bart13

Afraid it's difficult to know how to unwrap those debug lines and mak
any
sense of them. I was hoping though that the information might b
meaningful
to you, in particular indicate if any charts were not being picked u
as
expected, or a chart being picked up twice (strangely that's possibl
in a
For Each loop).

Did you try looping with the For...To...Next method and compare th
debug,
as I suggested.

I forgot to comment on this from your earlier post
---
You can group chartobjects with any other objects, eg rectangle o
other
chartobjects, by selecting them, right-click then group. Copying then
ungrouping can lead to duplicate names (particularly objects that ha
been
renamed from their given default).

Regards,
Peter T

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

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

...

 

[Peter T]

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

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

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

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

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

...

 

[Jon Peltier]

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
_______

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

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

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



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

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



...

 

[Peter T]

Aha! Gotcha!

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

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
_______

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

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



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

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]

Yes - very much so. The straight loop (as in "for i = 1 t
activesheet.chartobjects.count" ) has wrong results, just like ever
other attempt and regardless of whether the if test was being used o
not. There are 6 charts on the tab, only three show up in both th
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 exac
same matching values - every time. But both only return three names an
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 debu
print statement, its the same issue. Six charts in the tab, only thre
recognized

And I do betray my ancient status as a coder. The last time I did an
Basic coding, there was no such thing as a for each loop so the "for
= 1 to ..." is normal to me

I did double check the name box again for all six charts, and all th
names are both unique and do not contain punctuation either (unless a
underscore is considered punctuation)

I'm obviously missing something since it appears you're saying I hav
dupe names, but I have no way of which I'm aware to change th
duplicate ones nor is there any code that I can run which wil
distinguish the dupes.

The only way that works on new copied charts is not to change the nam
but rather hard code it into a select case set of statements based o
the "Chart ###" name

Are you saying that this is expected behavior and an open and old bu
(or something similar), and that I should just continue to do the har
coding and work around? I'm not trying to be accusatory, just in case

Regards
bar

I did try the normal loop based on max chart count and the result were
the same. Sorry, I just didn't post it since there was no new data.

I assume by "the normal loop" you mea
for i = 1 to activesheet.chartobjects.coun
although most people might consider "For..Each" as the "normal" way t
loop
objects

Are you absolutely sure the dubug results are different with th
different
methods of looping

Here's yet another way you can loop your chart

Sub test(
Dim shp As Shape, chtObj As ChartObject, cht As Char
For Each shp In ActiveSheet.Shape
If shp.Type = msoChart The
i = i +
Set chtObj = shp.DrawingObjec
Set cht = chtObj.Char
Debug.Print i, chtObj.Name, cht.Nam
End I
Nex
End Su

I would not expect this to work differently to a For i = 1 to
..Chartobjects.Count loo

From what you have described about chartobjects having been renamed an

copied I'm not surprised your For Each loop has failed to reference al

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 th

'DrawingObject' level; of which ChartObject is a sub type. Apart from
picking up the wrong object, in the case of duplicate names, names tha

include punctuation can be missed completely. Remember, we are talkin

about ChartObject name here, which is not (normally) same as
chartobject.Chart.Name (though chartObject & Shape names will be th
same)

Regards
Peter

[i

It was meaningful to me in the sense that it displayed exactly what
was expecting. Charts are just plain being missed in the loop, n
matter how the loop is constructed or even if the if statement i
removed. The ChartObjects are getting corrupted when renamed, and it
pretty consistent

I did try the normal loop based on max chart count and the result wer
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 th
properties menus but never took it any further since I seldom use an
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]

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

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

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

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]

The email with a link to the workbook will be on the way soon. The wee
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

I really would expect both the "for i = 1 to .chartobjects.count" an
the
For each shp in .Shapes loop (with if msoChart test) to wor
correctly.
Possibly there's something else in your own code at fault, or som
other bug
that we have all missed.

If you like you can send me a zipped stripped down non-sensitiv
version of
what you have. Make a copy of your book, prune as much as possible i
terms
of data, charts, sheets and not least code; leaving just enough to
demonstrate the problem.

Regards,
Peter T
pmbthornton gmail com

Yes - very much so. The straight loop (as in "for i = 1 to
activesheet.chartobjects.count" ) has wrong results, just lik every
other attempt and regardless of whether the if test was being use 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 th exact
same matching values - every time. But both only return three name 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, onl three
recognized.


And I do betray my ancient status as a coder. The last time I di any
Basic coding, there was no such thing as a for each loop so the "fo i
= 1 to ..." is normal to me.

I did double check the name box again for all six charts, and al the
names are both unique and do not contain punctuation either (unles an
underscore is considered punctuation).

I'm obviously missing something since it appears you're saying 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 th name
but rather hard code it into a select case set of statements base on
the "Chart ###" name.

Are you saying that this is expected behavior and an open and ol bug
(or something similar), and that I should just continue to do th hard
coding and work around? I'm not trying to be accusatory, just i case.

Regards,
bart


Peter T;678438 Wrote:--

I did try the normal loop based on max chart count and th results-
were--
the same. Sorry, I just didn't post it since there was no ne 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" wa 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 rename and

copied I'm not surprised your For Each loop has failed to referenc 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

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]

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

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

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

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

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]

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

 

[bart13]

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

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

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

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

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