Frustrating objectcollection problem... Items going missing

[Anonymous]

Hi all,

I've been working on a macro for quite a while now, and I'm REALLY close to completion. However I've just struck a problem I haven't seen before, and I'm struggling to understand.

To explain the macro very simply, it scans the activesheet in a drawing and generates a Collection Containing a list of LeaderNotes and Balloons, it then proceeds to replace all of them with the same LeaderNote/Balloon but on a different style. (I'm aware you can just change the style, but that's not the point). The way it works is for each item in the Collection it adds a new leader, and deletes the old one.

Problem is that when it deletes the old one, the corresponding entry from the Collection is also made invalid (It's still there, but all the values are "Application-defined or object-defined error"). I could live with this if it only corrupted the single corresponding value, however its corrupting 1 or 2 subsequent items in the object collection too.

I thought maybe I could Insert all the new leaders, then delete them all, but the same problem occurs when it goes back to delete everything.

Does anyone have any suggestions????? I'm at my wits end

[nmunro]

Iterating over a collection is invalidated if you change the collection during the iteration. Could you add the new ones first (add them to a separate collection), and then delete the entire original collection?

[Anonymous]

I'm not entirely sure what you're suggesting there. The collection I've created doesn't get modified (by my code) once it's created. It simple is used as a reference to place the new leader on the sheet, and to delete the existing leader. It seems to me as though there is some 'link' between the leader on the sheet, and the item in the collection, but I've not seen this behaviour before... I had tried creating a duplicate collection (though I didn't delete the original) but it showed the same behaviour...

[Anonymous]

I seem to have found a suitable workaround for now. Once I've added all the new leaders from the collection on to the sheet, I can then go through and delete all the old leaders in reverse order (last item in Collection first).