• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find Excel files containing specific functions

Alan_42

New Member
I'm using Excel 2003 and Windows 10.

I have an AddIn file called MyAddIns.XLA which contains many SUBs and functions, all of which work quite well.

I also have many spreadsheets which use a variety of these SUBs and functions.

But what I didn't do, and perhaps should have done, was to keep a record of which functions are used in which spreadsheets.

This hasn't been a problem up to now, but I have made a change to a function which means I need to alter the formulas that use it.

I'm fairly sure I've managed to find most of these, but would like to be absolutely certain.

I've tried:
1. the "File Search" facility within Excel 2003 but it won't find function names within formulas.
2. the Windows 10 Search facility - same problem.
3. a third party search facility - same problem.

As an example, I have a function called "ReverseMatch" and if I just enter that as text in a cell, then all the above methods find it. But when I use it in a formula, no results are found.

Any suggestions?
 
Alan_42
Upload a sample Excel file,
which has Your named 'ReverseMatch'-function used as it is in Your real file.
( no need Your AddIn-file )
 
OK - here you go attached.
I've included the code for the function in a text box.
It's very simple and works really well
 

Attachments

  • Sample.xls
    22 KB · Views: 3
I've just checked the various search facilities and all of them find the Sample file, because of the text box.
Delete the text box and the search fails.
 
Alan_42
Have You test this? ... or what did You mean?

Ps. I don't have Excel 2003 nor Windows at all.

Of couse I have.
I know how to search individual files.
Please read my original post.
What I'm trying to do is locate files on my computer that contain specific function references.
Thanks anyway. I'll just have to do it the hard way, by checking each file one by one. It'll take a long time.
 
Alan_42
I also have many spreadsheets which use a variety of these SUBs and functions. ... still one file.
I've tried: ... okay, this could be a hint.
Then ... have You tried to Search from Chandoo.org? ( ... top-right-corner )
That would possible to do with VBA
... but as You're using Windows ... I will skip this.
 
OK - so I've done a bit of research and discovered that what I'm trying to do is, I think, impossible.

This is because Excel does not store the textual names of functions within files. It uses pointers. Well of course it does!

So - I'm going to have to go through all my files and create a record of which workbooks use which functions. A long and tedious job but worth doing.

Advice, therefore, to anyone regularly using UDFs. Keep records.

This thread is closed.
 
Back
Top