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
"James Wellington" wrote:
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" wrote in message
...
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
"R.VENKATARAMAN" wrote:
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 wrote in message
...
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