Advanced VBA-Use IDE to determine if workbook name has any VBA references?

I have never learnt IDE so I am looking for some brilliant excelforum user who knows the mysteries of IDE!

I will describe the situation I have and what I want to achieve.

SITUATION:
I have a workbook which has a lot of workbook names (over 200) and a lot of these names are duds (i.e. they are obsolete due to revisions in the project).

OBJECTIVE:
Identify every workbook names that have no references in the VBA code AND have no cell dependents in the worksheets. I can then assume that these names can be deleted safely and proceed to do so manually.

So I need IDE to:

1. Loop through each name (including hiddens) in the workbooks name manager.
2. For each name in loop - search code to count number of occurences. If more than one, return TRUE, otherwise if no references found, return FALSE
3. For each name in loop - search workbook for any cell containing use of this name. If more than one found, return TRUE, else return FALSE
4. Somehow list the names that have returned FALSE and FALSE to the above two checks - either to worksheet or output as txt file.

things to consider
-workbook names may be referenced by other workbook names so you will have to build a tree
-the name of a workbook name might form part of another word so you could get false positives unless you can create a suitable regex
-at some point the amount of time you spend coding around problems caused by a development methodology has to cause you to review that methodology ;-)

Hi Joseph

Originally Posted by JosephP

workbook names may be referenced by other workbook names so you will have to build a tree

True that is possible. However, in the project I need to run the code on, no workbook name references any other workbook names. So we can forget that aspect for the purpose of solving my immediate practical problem.

Originally Posted by JosephP

the name of a workbook name might form part of another word so you could get false positives unless you can create a suitable regex

If I Ctrl + F in VBA, I can enter the name of a workbook name and manually search for it. So couldn't IDE automate what I am doing manually - by matching on whole word only?

Originally Posted by JosephP

at some point the amount of time you spend coding around problems caused by a development methodology has to cause you to review that methodology ;-)

Heh. I know what you are saying but this is a project that has grown over several years and been rehashed several times. I am single-handedly doing a (long overdue) overhaul. (And, before anyone suggests it, no it wouldn't be quicker to rebuild this from scratch).

yes you can match on whole word only but there's nothing to say you haven't got a routine that has the same name as a named range or a mention of the name in a comment that is not actually in the op code. this should get you started anyway although it is by no means foolproof

Please Login or Register  to view this content.

UPDATE:
JosephP PM'd me to let me know that he is unable to finish this code right at the moment. Is anyone else interested in taking over to finish it off? (Only one last thing left to achieve = the 3rd point in the opening post)

Originally Posted by JosephP

this should get you started anyway

EXCELLENT! :D
That code is incredible! I now have a list of 88 names not used anywhere in the code.

But how do I find out which of these are used anywhere in the workbook or not (Post #1, goal #3)?

2 months bump

3 months bump

Worst case, I could run a loop on each Name to check each Cell in the UsedRange for each WS in the WB - but that is extremely inefficient. Is there no other way??

4 months bump

5 months bump

I've finally solved it. (thanks to Ron de Bruins website).

No doubt the code could be made more efficient but at least I have something working...

Please Login or Register  to view this content.

Update

Please Login or Register  to view this content.

Requires the following functions

Please Login or Register  to view this content.