This question: Searching for function usage in Excel VBA got me thinking about a process for automating a search for all UDFs being used in a spreadsheet. Something along the lines of:
For Each UDF in Module1
If Cells.Find(What:=UDF.Name, After:="A1", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False) Then
MsgBox UDF.Name & " is in use"
End If
Next UDF
Is this possible and if so, what would the syntax be for looping through all UDFs?
I tweaked Dee's answer so that it only looks for functions. I also changed the code to search across all modules & across all worksheets in the active workbook. I also tweaked the code to highlight a cell containing a UDF when it's found. This code isn't thoroughly tested but seems to work for me. More details about my additions:
In order to restrict the search to functions, i.e. exclude subroutines, I passed the declaration line of the procedure to the ProcKindString, allowing it to distinguish between subroutines and functions. I'm working in a very large workbook with over 20 worksheets and about 30 modules where I'd say over 90% of procedures are subroutines, so this was a performance booster for me.
In order to search through all modules, I added a function which finds all modules in the current project. The function is called GetModules and returns a collection of modules. The top level function FindAllUDFs then iterates over these modules, and from there on it's pretty much Dee's code.
Okay, I'm going to do this the hard way, because I'm going to assume that you don't want to have to download the VBE classes from my repository to make this a bit easier to work with, but they're there as an example of what's possible regardless.
First, you'll need to add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library and allow VBA to access the editor by taking the following steps. (Assumes Office 2010)
Now we're ready to explore the code in the workbook, but first, some things to remember about what we're looking for here.
The code below works on the active vba project, but could be modified to take one as a parameter. It works with the quick test cases I provided below the
Run
sub, but I wouldn't guarantee that it works for all corner cases. Parsing is hard. This also just stores and prints the function signatures in theresults
collection. I imagine in reality you would want a function that returns them, so that you could loop through the collection looking for them in the workbook.