Forum Discussion
Hardcore users only - help needed to clean up a Workbook
This behavior could be related to hidden or "phantom" external links, but I'm not sure, as several pieces of information are not conclusive to me. These are notoriously tricky, especially with large, older workbooks that may have accumulated references or objects over time. In the pasted file, you will find your macro with a small change "For i = 1 To n-Loop" and a macro with "PhantomExternalLinks".
Maybe that helps.
If not, please include more detailed information, such as Excel version, operating system, storage medium, etc.
NikolinoDEThanks for engaging with this issue.
I do not believe that STEP -1 necessarily makes your code any more robust than STEP +1. It all depends on the implementation of the underlying Collection. To be truly safe, you would need to take a copy of the Collection. But your code and mine differ in that your deletions of Names actually change the underlying Collection; my mere change of an attribute of a Name does not.
My original code used FOR EACH when it ran up against this problem. I was merely clutching at straws when I changed it to FOR i. To no avail.
But your post has me confused. Were you able to replicate my issue on your system? If so, why would my environment (Win 10, M365, Build 2406) be of interest? More importantly, did your code solve the issue in your environment? For it sadly does not do so here.
Incidentally, your code does not so much chase phantom links as it attempts to work around the strange situation where in this particular Workbook thisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) suddenly collapses to 0 after some macro loops over all Names. That is, the link your code finds is no "phantom"; it is very real. (And I have no interest in deleting it - I need it.) My issue is that Excel loses track of it.