Retrieving List of Macros in Workbook

[NumberCruncher13]

Is there a way to retreive a list of macros stored in the VBA modules
of an excel workbook?

 

[Ashish Dutt Sharma]

Hi,
This and more... http://exceltips.vitalnews.com/E089_Managing_Macros.html
Cheers

 

[Bob Phillips]

Get hold of MZ Tools for VBA, it has a Statistics option that lists of the
procedures and gives you ... statistics on them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NumberCruncher13]

Hi,
This and more...http://exceltips.vitalnews.com/E089_Managing_Macros.html
Cheers





- Show quoted text -

Ashish,

Thanks for the link. I copied the code from here
http://exceltips.vitalnews.com/Pages/T0730_Generating_a_List_of_Macros.html

But when I run the macro, it looks like my computer stalls, or the
macro is taking too long to run. Have you used this before? Please
let me know if it works for you.

In the directions it states to make sure that the "Microsoft Visual
Basic for Applications Extensibility" is selected. Using my version
of MS Excel 2003, I checked "Microsoft Visual Basic for Applications
Extensibility 5.3"... not sure if that has anything to do with the
problem.

Thanks for you help!

 

[Bob Phillips]

This work fine for me, no need to worry about references

Sub ListMacros()
Const vbext_pk_Proc = 0
Dim VBComp As Object
Dim VBCodeMod As Object
Dim oListsheet As Object
Dim StartLine As Long
Dim ProcName As String
Dim iCount As Integer

Application.ScreenUpdating = False
On Error Resume Next
Set oListsheet = ActiveWorkbook.Worksheets.Add
iCount = 1
oListsheet.Range("A1").Value = "Macro"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
oListsheet.[a1].Offset(iCount, 0).Value = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iCount = iCount + 1

StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set VBCodeMod = Nothing
Next VBComp

Application.ScreenUpdating = True
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NumberCruncher13]

This work fine for me, no need to worry about references

Sub ListMacros()
Const vbext_pk_Proc = 0
Dim VBComp As Object
Dim VBCodeMod As Object
Dim oListsheet As Object
Dim StartLine As Long
Dim ProcName As String
Dim iCount As Integer

Application.ScreenUpdating = False
On Error Resume Next
Set oListsheet = ActiveWorkbook.Worksheets.Add
iCount = 1
oListsheet.Range("A1").Value = "Macro"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
oListsheet.[a1].Offset(iCount, 0).Value = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iCount = iCount + 1

StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set VBCodeMod = Nothing
Next VBComp

Application.ScreenUpdating = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks for the link. I copied the code from here
http://exceltips.vitalnews.com/Pages/T0730_Generating_a_List_of_Macro...

But when I run the macro, it looks like my computer stalls, or the
macro is taking too long to run. Have you used this before? Please
let me know if it works for you.

In the directions it states to make sure that the "Microsoft Visual
Basic for Applications Extensibility" is selected. Using my version
of MS Excel 2003, I checked "Microsoft Visual Basic for Applications
Extensibility 5.3"... not sure if that has anything to do with the
problem.

Thanks for you help!

Bob -

I copied your code into the vba editor and noticed there's a syntax
error for the following line:
"Set VBCodeMod ="

Did that work for you?

Thanks again for you help.

 

[Bob Phillips]

Yes, that is not a coding error, but is caused by the NG wrapping the text.
One of the lines of code was split into two, causing the problem.

Hopefully this is better

Sub ListMacros()
Const vbext_pk_Proc = 0
Dim VBComp As Object
Dim VBCodeMod As Object
Dim oListsheet As Object
Dim StartLine As Long
Dim ProcName As String
Dim iCount As Integer

Application.ScreenUpdating = False
On Error Resume Next
Set oListsheet = ActiveWorkbook.Worksheets.Add
iCount = 1
oListsheet.Range("A1").Value = "Macro"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = _
ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
oListsheet.[a1].Offset(iCount, 0).Value = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iCount = iCount + 1

StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set VBCodeMod = Nothing
Next VBComp

Application.ScreenUpdating = True
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

This work fine for me, no need to worry about references

Sub ListMacros()
Const vbext_pk_Proc = 0
Dim VBComp As Object
Dim VBCodeMod As Object
Dim oListsheet As Object
Dim StartLine As Long
Dim ProcName As String
Dim iCount As Integer

Application.ScreenUpdating = False
On Error Resume Next
Set oListsheet = ActiveWorkbook.Worksheets.Add
iCount = 1
oListsheet.Range("A1").Value = "Macro"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
oListsheet.[a1].Offset(iCount, 0).Value = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iCount = iCount + 1

StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set VBCodeMod = Nothing
Next VBComp

Application.ScreenUpdating = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

On Dec 4, 11:20 am, Ashish Dutt Sharma
Hi,
This and
more...http://exceltips.vitalnews.com/E089_Managing_Macros.html
Cheers

:
Is there a way to retreive a list of macros stored in the VBA
modules
of an excel workbook?- Hide quoted text -

- Show quoted text -

Thanks for the link. I copied the code from here
http://exceltips.vitalnews.com/Pages/T0730_Generating_a_List_of_Macro...

But when I run the macro, it looks like my computer stalls, or the
macro is taking too long to run. Have you used this before? Please
let me know if it works for you.

In the directions it states to make sure that the "Microsoft Visual
Basic for Applications Extensibility" is selected. Using my version
of MS Excel 2003, I checked "Microsoft Visual Basic for Applications
Extensibility 5.3"... not sure if that has anything to do with the
problem.

Thanks for you help!

Bob -

I copied your code into the vba editor and noticed there's a syntax
error for the following line:
"Set VBCodeMod ="

Did that work for you?

Thanks again for you help.