Excel

List All Macros in Active Workbook

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

On running the code a message box will list every procedure in the workbook 

Discussion:

You can go to 'Tools/Macro/Macros' and look at your list of macros, but this only lists macros that begin with 'Sub', so any Private Subs and Workbook or Worksheet macros are not listed. Where-as this will list every one of those. 

Code:

instructions for use

			

Option Explicit Sub ListOfMacros() On Error Resume Next '< error = reference already set 'set reference to Microsoft Visual Basic for Applications 'Extensibility 5.3 ThisWorkbook.VBProject.References.AddFromGuid _ "{0002E157-0000-0000-C000-000000000046}", 5, 3 'now get the list of macros Call GetTheList End Sub Private Sub GetTheList() Dim N&, Count&, MyList(200), List$ Dim Component As VBComponent 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) Debug.Print MyList(N) List = List & vbCr & MyList(N) If Count < .CountOfLines Then N = N + 1 Loop End With N = N + 1 Next MsgBox List, , "List of Macros" End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor (VBE)
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select File/Close and Return To Microsoft Excel
 

Test the code:

  1. Select Tools/Macro/Macros.../ListOfMacros/Run
  2. This requires that the reference to Microsoft Visual Basic for Applications Extensibility 5.3 is set...
  3. On versions earlier than Office 2000 this may be a different number than 5.3, so if you get an error, go to the VBE window/Tools/References and make sure that
  4. Microsoft Visual Basic for Applications Extensibility x.x and (possibly)...
  5. Microsoft Forms 2.0 Object Library are set.
 

Sample File:

ListMacrosInBook.zip 8.43KB 

Approved by mdmackillop


This entry has been viewed 275 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express