Way to output list of macros to file
 

I'm looking for a way to generate a list of all the macros contained within a
workbook. I've found two suggestions online but haven't been able to get
either to work. Is anyone aware of an easy to get the list of names?

PJZ

Way to output list of macros to file
 

I went to cpearson website and got this code. You have to change FName in
the code below and do two things in the comments in the code below.

1) Add the reference library
2) change your macro security.


Sub DeleteAllVBACode()
'1) add following library as reference
'using tools - references
'Microsoft Visual Basic for Applications Extensibility 5.3

'2) Next, you need to enable programmatic access to the VBA Project.
'In Excel 2003 and earlier,
'go the Tools menu (in Excel, not in the VBA editor),
'choose Macros and then the Security item.
'In that dialog, click on the Trusted Publishers tab and
'check the Trust access to the Visual Basic Project setting.

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim ProcKind As VBIDE.vbext_ProcKind

Set sht = ThisWorkbook.ActiveSheet
FName = "d:\Visual Basic\text.xls"
Set bk = Workbooks.Open(Filename:=FName)
Set VBProj = bk.VBProject
RowCount = 1
For Each VBComp In VBProj.VBComponents

Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Do Until LineNum = .CountOfLines
sht.Range("A" & RowCount) = ProcName
sht.Range("B" & RowCount) = ProcKindString(ProcKind)
RowCount = RowCount + 1

LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) + 1

ProcName = .ProcOfLine(LineNum, ProcKind)
Loop
End With

Next VBComp
bk.Close savechanges:=False
End Sub
Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
Select Case ProcKind
Case vbext_pk_Get
ProcKindString = "Property Get"
Case vbext_pk_Let
ProcKindString = "Property Let"
Case vbext_pk_Set
ProcKindString = "Property Set"
Case vbext_pk_Proc
ProcKindString = "Sub Or Function"
Case Else
ProcKindString = "Unknown Type: " & CStr(ProcKind)
End Select
End Function


"PJ" wrote:

I'm looking for a way to generate a list of all the macros contained within a
workbook. I've found two suggestions online but haven't been able to get
either to work. Is anyone aware of an easy to get the list of names?

PJZ

Way to output list of macros to file
 

Hi Joel,

That looks a little different then the code I already tried so it's worth a
shot. I will test it out when I get back to work on Monday.

Thank you.

"joel" wrote:

I went to cpearson website and got this code. You have to change FName in
the code below and do two things in the comments in the code below.

1) Add the reference library
2) change your macro security.


Sub DeleteAllVBACode()
'1) add following library as reference
'using tools - references
'Microsoft Visual Basic for Applications Extensibility 5.3

'2) Next, you need to enable programmatic access to the VBA Project.
'In Excel 2003 and earlier,
'go the Tools menu (in Excel, not in the VBA editor),
'choose Macros and then the Security item.
'In that dialog, click on the Trusted Publishers tab and
'check the Trust access to the Visual Basic Project setting.

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim ProcKind As VBIDE.vbext_ProcKind

Set sht = ThisWorkbook.ActiveSheet
FName = "d:\Visual Basic\text.xls"
Set bk = Workbooks.Open(Filename:=FName)
Set VBProj = bk.VBProject
RowCount = 1
For Each VBComp In VBProj.VBComponents

Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Do Until LineNum = .CountOfLines
sht.Range("A" & RowCount) = ProcName
sht.Range("B" & RowCount) = ProcKindString(ProcKind)
RowCount = RowCount + 1

LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) + 1

ProcName = .ProcOfLine(LineNum, ProcKind)
Loop
End With

Next VBComp
bk.Close savechanges:=False
End Sub
Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
Select Case ProcKind
Case vbext_pk_Get
ProcKindString = "Property Get"
Case vbext_pk_Let
ProcKindString = "Property Let"
Case vbext_pk_Set
ProcKindString = "Property Set"
Case vbext_pk_Proc
ProcKindString = "Sub Or Function"
Case Else
ProcKindString = "Unknown Type: " & CStr(ProcKind)
End Select
End Function


"PJ" wrote:

I'm looking for a way to generate a list of all the macros contained within a
workbook. I've found two suggestions online but haven't been able to get
either to work. Is anyone aware of an easy to get the list of names?

PJZ