If you use a lot of VBA procedures in your Excel work, you might find a need to list all of those procedures. In order to accomplish this, you can copy this procedure into a module in your workbook in the VBE. Under Tools, References, check the Microsoft Visual Basic for Applications Extensibility. When you run it, you will get a 2 column list in the active worksheet. The first column will contain the name of the module and the second column will have the procedure name.
Option Explicit
Sub GetTheList()
Dim N&, Count&, MyList(2000), List$
Dim Component As Variant
For Each Component In ActiveWorkbook. _
VBProject.VBComponents
With Component.CodeModule
Count = .CountOfDeclarationLines + 1
Do Until Count >= .CountOfLines
MyList(N) = .ProcOfLine(Count, _
vbext_pk_Proc)
Count = Count + .ProcCountLines _
(.ProcOfLine(Count, vbext_pk_Proc), _
vbext_pk_Proc)
ActiveSheet.Range(“a” & N).Value = .Name
ActiveSheet.Range(“b” & N).Value = MyList(N)
List = List & vbCr & MyList(N)
If Count < .CountOfLines Then N = N + 1
Loop
End With
N = N + 1
Next
End Sub
HTH!
Pingback: #Excel Super Links #58 – shared by David Hager | Excel For You
Pingback: #Excel Super Links #150 – Special Edition | Excel For You