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

"Bob Phillips" wrote:

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 wrote in message
...
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