Solved

Creating a list of all macros in a workbook.

Posted on 2012-03-26
6
250 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 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

15 Experts available now in Live!

Get 1:1 Help Now