#Excel Short and Sweet Tip #20 (Create VBA Procedure List) by David Hager

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!

Advertisements

2 thoughts on “#Excel Short and Sweet Tip #20 (Create VBA Procedure List) by David Hager

  1. Pingback: #Excel Super Links #58 – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s