Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Generating a List of Macros

Once you start writing Excel macros, it is easy to get quite a few of them in a workbook. At some point you may want to generate a list of macros in your workbook. There is no intrinsic way within Excel to create a list of macros. You can, however, create a macro that will list your macros. (Sort of sounds redundant, doesn't it?)

As an example, consider the following macro, which steps through all the projects in your workbook to garner all the macro names and place them in a worksheet:

Sub ListMacros()
    Dim VBComp As VBComponent
    Dim VBCodeMod As CodeModule
    Dim oListsheet As Object
    Dim StartLine As Long
    Dim ProcName As String
    Dim iCount As Integer

    Application.ScreenUpdating = False
    On Error Resume Next
    Set oListsheet = ActiveWorkbook.Worksheets.Add
    iCount = 1
    oListsheet.[a1] = "Macro"

    For Each VBComp In ThisWorkbook.VBProject.VBComponents
        Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
        With VBCodeMod
            StartLine = .CountOfDeclarationLines + 1
            Do Until StartLine >= .CountOfLines
                oListsheet.[a1].Offset(iCount, 0).Value = _
                  .ProcOfLine(StartLine, vbext_pk_Proc)
                iCount = iCount + 1

                StartLine = StartLine + _
                  .ProcCountLines(.ProcOfLine(StartLine, _
                   vbext_pk_Proc), vbext_pk_Proc)
            Loop
        End With
        Set VBCodeMod = Nothing
    Next VBComp

    Application.ScreenUpdating = True
End Sub

In order to use this macro, you must make sure you have the Microsoft VBA extensibility reference set. To do this, follow these steps:

  1. In the VBA Editor, choose References from the Tools menu. The References dialog box is displayed. (See Figure 1.)
  2. Figure 1. The References dialog box.

    ***Insert Figure X
  3. Scroll through the list of Available References and make sure the Microsoft Visual Basic for Applications Extensibility check box is selected.
  4. Close the dialog box.

When you run the macro, it adds a new worksheet to your workbook, and then lists the names of all the macros in all the modules in the workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2715) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

George Corbett    25 Sep 2016, 14:40
Alan,
I wanted to thank you very much for your excellent software which lists all macros in an Excel Workbook.

I have been looking for some software to last all macros for quite a while. Yours is the only one which worked,
and it worked the first time out!

I do not understand all that is going on, but it makes no difference as I can now properly document my projects.

Thanks again for a great piece of software.

George Corbett
Gail    03 Jun 2015, 16:45
GREAT tip...just what I have been looking for to see all macro names. I have over 200 macros and people are wanting some of them...it's so easy for them to get out of hand. Now I can go in and organize them.
Thank you
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.