Solved: List all functions in workbook

[brucebh]

I would like to produce a list of all function names/Sub procedures names within a workbook. There must be some way of doing this as it's what the Tools/Macro/Macros menu item does in Excel (at least for Subs). Maybe they exist as a collection, but if so I can't see how to reference the "functions" collection.

Any suggestions?

[CreganTur]

You can use MZTools (http://www.mztools.com/index.aspx) to create documentation that lists all Functions/Subs within a workbook.

Check it out- it may meet your needs.

[brucebh]

Thanks, this looks like an interesting tool, but I want to display the list of functions at run-time for the user, I'm not looking for listing them in design mode. What code does MZ Tools use to grab the list of functions to create the documentation?

What I'm actually trying to accomplish is to provide a user interface with a drop down list of all macros (or functions) the user can run. You're probably thinking "Why not just get the list up from the Tolls/Macro/Macros menu in Excel?" but I want to filter the list so that the user only sees certain macros. I need to grab all the macro names so that I can then put some code in to filter them.

[jfournier]

You can try using this function I've written based on something I found on the subject; I don't remember where. It will return a collection of all sub and function names in ThisWorkbook's VBProject. You have to have a reference set to : C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB which is for "Microsoft Visual basic for Applications Extensibility" for the code to work. Also any computer running Excel must have "Trust access to Visual Basic Project" checked on in Tools->Macros->Security under the "Trusted Publishers" tab. If this is an issue you can just save all the names to a hidden worksheet and update it whenever you update your code and send it out. I've done this once and it seems to work well enough.

Function ListSubsAndFuncs() As Collection
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBMod As VBIDE.CodeModule

Dim result As Collection
Set result = New Collection

Set VBProj = ThisWorkbook.VBProject

Dim FuncDec As String
For Each VBComp In VBProj.VBComponents
Set VBMod = VBComp.CodeModule
For i = 1 To VBMod.CountOfLines
'Split the current line by space characters so we can see what sort of line of code it is.
Dim WordsInLine() As String
WordsInLine = Split(VBMod.Lines(i, 1), " ")
For j = LBound(WordsInLine) To UBound(WordsInLine)
If LCase(WordsInLine(j)) = "sub" Or LCase(WordsInLine(j)) = "function" Then
'This line is a subroutine declaration
If j + 1 <= UBound(WordsInLine) Then
Dim FuncName As String
'find out if there's a parenthesis included in the declaration, if so get rid of it
If InStr(1, WordsInLine(j + 1), "(") > 0 Then
FuncName = Left(WordsInLine(j + 1), InStr(1, WordsInLine(j + 1), "(") - 1)
Else
FuncName = WordsInLine(j + 1)
End If
result.Add CVar(FuncName)
End If
End If
Next j
Next i
Next VBComp
Set VBProj = Nothing
Set VBMod = Nothing
Set VBComp = Nothing
Set VBProj = Nothing

Set ListSubsAndFuncs = result
End Function

I tried replacing all the VBIDE declarations with just "Object" and removing the reference, and that seems to work just the same.

[brucebh]

Excellent jfournier, works like a charm!:clap:

Would never have thought of approaching it this way as a line by line search through code. Not the perfect solution as users have to have the "Trusted Publishers" setting clicked, so not great for general security, but pretty good nonetheless.

Thanks.