VBA Code that will find and list VBA functions and procedures.

This is the only automatic Excel name find utility I know of, but I have built a utility to find Excel files and have modules to pull lists if items to be used in other code. So, I know you can write code to pull the data you are looking for!

On your report sheet, off to the Right of your report pick two columns. Select the left column and a cell in that column that you want to start the list of all your named ranges. Then, from the menu select:
Insert
Name
Paste
Paste List

Excel will create a database list of all the named ranges used in your workbook. The first of your two columns will be the range name the second column will be the "Sheet Name and Range."

You can the reference the list with VLOOKUP to return the address of the Range Name used in your formula. JSW

Create a User Form (UserForm1) containing a List Box (ListBox1), then run this macro :-

Sub ListMacros()
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
UserForm1.ListBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Next VBComp
UserForm1.Show
End Sub

You will need to create a reference to MS VBA Extensibilty 5.3 (in VBE go to Tools>References)