Solved

Creating a list of all macros in a workbook.

Posted on 2012-03-26
6
251 Views
Last Modified: 2012-03-31
I would like to create a list of macros in my workbook, based on something like this:

Dim ListPos As Range, macroName As String
Set ListPos = cells([ListTop], 1).End(xlDown).Offset(1, 0)
ListPos = macroName

Of course, this may be not at all the way to go. But if it is, the two big questions are:
     1) How do I define MacroName to be the name of each macro in succession
     2) How do I set up the loop

i should be able to figure out the second one myself, but I can't.

Thanks,
John
0
Comment
Question by:gabrielPennyback
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37768295
You're going to need code like this and perhaps 15-30+ minutes of an Expert's time - assuming that Expert has gone here, before (I'm off to a meeting, but will check back in a bit):

http://www.cpearson.com/Excel/vbe.aspx

Do you want all public modules, class modules, including Sheets and ThisWorkbook?

So, output would be something like:

Module   MacroType   MacroName
Module1  Function     getsomething()
etc.
etc..

???

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37769688
John - I did a bit more than you asked, but felt this would be a useful utility as well.  As such, I created this file with some add-in menu items so you can run the macro against any active workbook.  

The actual code creates an Output Tab called "Enumerated_Macros" and details whatever it finds in that workbook in the form of Module, Procedure, Type - that way any Sheet or ThisWorkbook, Public Module or Class Module might be listed if it exists.

Here's the primary code:
Sub generateOutput()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim i As Long
Dim vSubDetail As Variant

    Set wkb = ActiveWorkbook
    On Error Resume Next
    Set wks = wkb.Worksheets("Enumerated_Macros")
    If Err.Number <> 0 Then
        Set wks = wkb.Worksheets.Add(after:=wkb.Worksheets(wkb.Worksheets.Count))
        wks.Name = "Enumerated_Macros"
    End If
    On Error GoTo 0
    
    'clear the output sheet
    wks.Cells.Clear
    
    'get subs from every module in the workbook
    For Each vSubDetail In getSubsFromProject(ActiveWorkbook)
        wks.Range("A2").Offset(i, 0).Resize(, 3).Value = Split(vSubDetail, ",")
        i = i + 1
    Next vSubDetail
    
    'make it pretty
    
    With wks.Range("A1:C1")
        .Value = Split("Code Module, Procedure Name, Procedure Type", ",")
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With
    
    wks.Range("B2").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True

End Sub

Open in new window


See attached workbook and if you like it you can actually save it as an add-in, always there for you.

-------------------------------------------------

For your specific request, you can use the call to getSubsFromProject(ActiveWorkbook) to achieve the end you are looking for.  If you just want that, copy over the module called VBA_Helper which synchronizes the VBE Editor and has the functions needed to make this all work.

Here's how I would write your code:
Sub getMyMacros()
Dim ListPos As Range, macroName As String
Dim vSubDetail As Variant '0-module name, 1-sub/function name, 2-sub/function type

    For Each vSubDetail In getSubsFromProject(ActiveWorkbook)
    
        Set ListPos = Cells(Rows.Count, [ListTop].Column).End(xlUp).Offset(1, 0)
        ListPos = Split(vSubDetail, ",")(1) 'get the sub name
    
    Next vSubDetail
    
End Sub

Open in new window


Note, your statement:

Set ListPos = cells([ListTop], 1).End(xlDown).Offset(1, 0)

Would result in an error if there were not other values below the ListTop range (end down would hit the bottom of the worksheet, and the Offset(1,0) would blow it out).

See attached.

Cheers,

Dave
EnumerateModules-r1.xlsm
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 37772427
You know everyone once in a while I start thinking of myself as somewhat of an expert in VBA (the people I work with fortunately don't know any better), and then I encounter something like your workbook and that snaps me back into reality! :- )

Thanks, Dave, this is quite amazing.

Now come the part where i look the gift horse in the mouth and ask for one more thing, my reason for asking the question in the first place was that there are about 50 macros nested and sub-nested inside my main macro and I wanted something that would add the name of each macro to a list at the start of each macro.

So my final question would be: is there a generic macro that will automatically add its name to the list? As opposed to repeating the code 50 times, one for each macro like this:

Sub StampOpen()
[MacroList].End(xlDown).Offset(1, 0) = "StampOpen"
[start] = Now()
Dim str As String
str = Environ("Username")
[UserStamp] = "Run by " & Application.VLookup(str, Range("Users"), 2, False) & _
    " @ " & Format(Now, "h:mm AM/PM") & " on " & Format(Now, "m/d/yyyy")
End Sub

Something that would say: [MacroList].End(xlDown).Offset(1, 0) = thisMacroName

If this requires more than a moment's thought for you please let me know and I'll post it as a new question.

In the meantime, thanks for this awesome macro and workbook!

- John
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 37772486
Thanks for your kind words.  I, unfortunately, don't seem to have command of the English language as I'm having a hard time understanding the question.

Perhaps if you uploaded a sample workbook with sample output?

;)

Dave
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 37773511
I'll put something together and upload it, thanks.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37792052
So, you have a macro that calls other macros and you want a list of the macro that each macro calls?

E.g.,

Sub Main()

  call Macro1

  call macro2

End Sub

Sub Macro1
  Call Macro3
End Sub
Sub Macro2
End sub
Sub Macro3
End Sub

Then you want in your sheet something like:

Main
  Macro1
     Macro3
  Macro2

???

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now