Can VBA be used to list macros

[Price@discussions.microsoft.com James Price]

I want to be able to use VBA to find out the list of macro names in a
workbook. I have used code (see below) that can give me the module names,
and also tried with the CodePane function, but I think I'm being a bit thick
and missing something.

Answers gratefully received

Thanks

James

Sub FindMacros()
Dim vbComp As VBIDE.VBComponent
Dim wb As Workbook

Set wb = ThisWorkbook
For Each vbComp In wb.VBProject.VBComponents
Debug.Print vbComp.Type & " " & vbComp.Name
Next

End Sub

[Rob Bovey]

Hi James,

Download my VBA Code Documentor utility from the web site below my sig.
The code is unprotected, so you can dig in and see how it works.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"James Price" <James Pr...@discussions.microsoft.com> wrote in message
news:60FDBD76-87C7-4C54...@microsoft.com...

[Bob Phillips]

Try this

'---------------------------------------------------------------
Public Function ListMacros(Optional RunTypesOnly As Boolean = True, _
Optional PublicOnly As Boolean = False)
'---------------------------------------------------------------
' Function: List all macros in all workbook projects
' Synopsis: Loops through the designated module processing
' each procedure by:
' - get the number of lines in the procedure
' - searches for the End statement in procedure
' to identify its line number
' - determines the procedure type
' - move onto next procedure
'---------------------------------------------------------------
Const COMPONENT_MODULE As Long = 1
Dim oCodeModule As Object, oComponent As Object
Dim oWb As Workbook
Dim fStart As Boolean
Dim iStart As Long, iCurrent As Long
Dim cLines As Long, cProcs As Long
Dim ProcType As Long '0 Property, 1 Sub, 2 Function
Dim sProcName As String
Dim lProcKind As Long
Dim aryProcs

ReDim aryProcs(1 To 3, 1 To 1)

For Each oWb In Application.Workbooks
Debug.Print oWb.Name

For Each oComponent In oWb.VBProject.VBComponents
Debug.Print "___" & oComponent.Name

If oComponent.Type = COMPONENT_MODULE Then
With oComponent.CodeModule

iStart = .CountOfDeclarationLines + 1

Do Until iStart >= .CountOfLines
'get the procedure name and count of line
'.ProcOfLine modifies ProcKind to type
sProcName = .ProcOfLine(iStart, lProcKind)
cLines = .ProcCountLines(sProcName, lProcKind)
Debug.Print "______" & sProcName

iCurrent = iStart - 1
Do
iCurrent = iCurrent + 1
fStart = .Lines(iCurrent, 1) Like "*Sub *" Or _
.Lines(iCurrent, 1) Like "*Function *"
Or _
.Lines(iCurrent, 1) Like "*Property *"
Loop Until fStart

'determine procedure type
If .Lines(iCurrent, 1) Like "*Sub *" Or _
.Lines(iCurrent, 1) Like "*Function *" Then

If Not PublicOnly Or Not .Lines(iCurrent, 1)
Like "*Private *" Then
If RunTypesOnly Then
If InStr(.Lines(iCurrent, 1), "()") Then
cProcs = cProcs + 1
ReDim Preserve aryProcs(1 To 3, 1 To
cProcs)
aryProcs(1, cProcs) = oWb.Name
aryProcs(2, cProcs) =
oComponent.Name
aryProcs(3, cProcs) = sProcName
End If
Else
cProcs = cProcs + 1
ReDim Preserve aryProcs(1 To 3, 1 To
cProcs)
aryProcs(1, cProcs) = oWb.Name
aryProcs(2, cProcs) = oComponent.Name
aryProcs(3, cProcs) = sProcName
End If
End If

End If

'onto the next procedure
iStart = iStart + _
.ProcCountLines(sProcName, lProcKind)
Loop
End With 'oComponent
End If
Next oComponent
Next oWb

ListMacros = aryProcs

End Function

--
HTH

Bob Phillips

"James Price" <James Pr...@discussions.microsoft.com> wrote in message
news:60FDBD76-87C7-4C54...@microsoft.com...

[jamesppre...@discussions.microsoft.com]

Thanks again for this Bob - spot on

I like to think I know a little about MS Visio - if per chance you have a
query please feel free to contact me at jam...@premiercs.co.uk

Cheers

James

[jamesppre...@discussions.microsoft.com]

Thanks for your help Bob

James

[Elizabeth]

Bob:
I am new to VBA. I would like to list, on a new worksheet, all of the
macros in a file. When I copy & paste your code into a VBE module of one of
my files, then run it, it returns the Macros box (same as Tools > Macro >
Macros...). Is this the intended result? Is there some way to tweak this
code to make it list the macros in a worksheet? Thank you for any help
you're willing to provide.
Elizabeth

[R.VENKATARAMAN]

I have in my notes reference to one of David Mcritchie's pages
I have the codes also
the name of the subs are
ListFunctionsAndSubs
ShowSubOrFunction

urls are
'Documented in http://www.mvps.org/dmcritchie/excel/buildtoc.htm
'Coding: http://www.mvps.org/dmcritchie/excel/code/listfsubs.txt
'Coding: http://www.mvps.org/dmcritchie/excel/code/buildtoc.txt
'Coding: http://www.mvps.org/dmcritchie/excel/code/gotostuff.txt
'My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
I think the first url will give your the other links.
-
remove $$$ from email addresss to send email

=======================

Elizabeth <Eliz...@discussions.microsoft.com> wrote in message
news:516E72F9-258A-46C3...@microsoft.com...

[Elizabeth]

Thank you for your response. I tried it, but encountered the following error:
"Compile error: User-defined type not defined"
for the following line of code: Dim VBComp As VBComponent

When I place my cursor on VBComponent & hit F1, Microsoft VB Help returns
"Keyword Not Found". I am not familiar with this property & cannot locate it
in 3 reference books I have.

Am I just too new at VBA to make this work, or is there a simple reason /
solution to this. Thanks again.
Elizabeth

[James Wellington]

Elizabeth,
You need to set the following reference...Microsoft Visual Basic for
Application Extensibility

To do this, in the vba goto tools, references and find the one above and
check it.

James

"Elizabeth" <Eliz...@discussions.microsoft.com> wrote in message

news:154DDB89-4B08-43F0...@microsoft.com...

[Elizabeth]

James:
I checked the "Microsoft Visual Basic for Application Extensibility 5.3" box
as you instructed, and then reran the macro. I got past the VBComponent
property, but am now receiving the following error on the "For Each VBComp In
myBook.VBProject.VBComponents" line of code:
"Run-time error '1004': Programmatic access to Visual Basic Project is not
trusted". Once again, I have consulted VBE Help and my 3 reference books
without finding a solution. Thank you for any help you can provide.
Elizabeth

[Dave Peterson]

Back to excel.
Tools|macro|security|trusted publishers tab|check that Trust access to Visual
basic project.

This is a user by user setting (FYI). If you change your setting, it doesn't
affect anybody else.

--

Dave Peterson

[Elizabeth]

Dave:
IT WORKS!!! This is so great! When I received the last error message I had
confirmed that the project was digitally signed, but didn't know about the
"Trust access..." box. Thank you.
I am in awe of the level of knowledge that everyone has who was involved in
this string, as well as everyone's willingness to share their knowledge. I
am now going to foward this macro to friends who know even less about VBA
than I do.
Elizabeth

[Bob Phillips]

That is actually incorrect. I coded it late-bound, so that is not needed.

--
HTH

Bob Phillips

"James Wellington" <james_we...@comcast.net> wrote in message
news:%23IUIqrE...@TK2MSFTNGP10.phx.gbl...

[Elizabeth]

Bob:
I used code from R. Venkataraman (found at
http://www.mvps.org/dmcritchie/excel/code/listfsubs.txt); I did not use the
code that you provided since I was having difficulty getting it to work (100%
due to my inexperience).
Elizabeth

[Bob Phillips]

Okay, sorry I thought it was my contribution.

Bob

"Elizabeth" <Eliz...@discussions.microsoft.com> wrote in message

news:6A097818-4A90-4B6A...@microsoft.com...