Can VBA count how many macros I have?

[Ed]

I have collected quite an assortment of macros over the last 2 years. Is
there a VBA method for counting how macros there are, with several modules
holding five or more macros?
Ed

 

[Michael Bednarek]

I have collected quite an assortment of macros over the last 2 years. Is
there a VBA method for counting how macros there are, with several modules
holding five or more macros?

Try this: (needs a reference to "Microsoft Visual Basic for Applications
Extensibility" (VBE6EXT.OLB))

Sub CountMacros()

Dim objVBProj As VBProject
Dim objVBComps As VBComponents
Dim objVBComp As VBComponent
Dim objVBMod As CodeModule
Dim lngLine As Long

For Each objVBProj In VBE.VBProjects
Debug.Print "Project: " & objVBProj.Name
Set objVBComps = objVBProj.VBComponents
For Each objVBComp In objVBComps
Debug.Print " Component: " & objVBComp.Name
Set objVBMod = objVBComp.CodeModule
With objVBMod
lngLine = .CountOfDeclarationLines + 1
Do Until lngLine >= .CountOfLines
Debug.Print " Procedure: " & .ProcOfLine(lngLine, vbext_pk_Proc)
lngLine = lngLine + .ProcCountLines(.ProcOfLine(lngLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next objVBComp
Next objVBProj
End Sub

See also: <http://www.cpearson.com/excel/vbe.htm> and
<http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=307>.

 

[Ed]

Very sweet! Thank you!!
Ed

Try this: (needs a reference to "Microsoft Visual Basic for Applications
Extensibility" (VBE6EXT.OLB))

Sub CountMacros()

Dim objVBProj As VBProject
Dim objVBComps As VBComponents
Dim objVBComp As VBComponent
Dim objVBMod As CodeModule
Dim lngLine As Long

For Each objVBProj In VBE.VBProjects
Debug.Print "Project: " & objVBProj.Name
Set objVBComps = objVBProj.VBComponents
For Each objVBComp In objVBComps
Debug.Print " Component: " & objVBComp.Name
Set objVBMod = objVBComp.CodeModule
With objVBMod
lngLine = .CountOfDeclarationLines + 1
Do Until lngLine >= .CountOfLines
Debug.Print " Procedure: " & .ProcOfLine(lngLine, vbext_pk_Proc)
lngLine = lngLine + .ProcCountLines(.ProcOfLine(lngLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next objVBComp
Next objVBProj
End Sub

See also: <http://www.cpearson.com/excel/vbe.htm> and
<http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=307>.

 

[Ed]

Michael: I get a "Sub or Function not defined" error on:
lngLine = lngLine + .ProcCountLines(.ProcOfLine(lngLine,
vbext_pk_Proc), vbext_pk_Proc)
The offending item seems to be .ProcCountLines (at least, that's what gives
the error on mouse-over when the line is highlighted yellow).
I have the reference to VBA Extensibility 5.3 set. What else might I be
missing?
Ed

 

[Michael Bednarek]

First a few things to your posting style:
1) Don't change the subject when you continue a thread; you might
never get a response.
2) Don't top-post when the previous contributor bottom-posted
(the preferred method).
3) Remove the signature of the previous posts.
4) Snip unnessesary line from previous posts.

Michael: I get a "Sub or Function not defined" error on:
lngLine = lngLine + .ProcCountLines(.ProcOfLine(lngLine,
vbext_pk_Proc), vbext_pk_Proc)
The offending item seems to be .ProcCountLines (at least, that's what gives
the error on mouse-over when the line is highlighted yellow).
I have the reference to VBA Extensibility 5.3 set. What else might I be
missing?

[snip]

I hope you unwrapped the lines properly in case they got wrapped at your
end - they left here unwrapped and showed as such in my newsreader. Lines
which start in position 1 have been accidentally wrapped. All code below
starts in position 3 (plus indents).

I ran the macro in MS Word, Excel, and Access. There was a problem in
Excel with protected projects and apparently it needed the Application.
qualifier for the VBE.Projects object, so here is my amended code:

Sub CountMacros()

Dim objVBProj As VBProject
Dim objVBComps As VBComponents
Dim objVBComp As VBComponent
Dim objVBMod As CodeModule
Dim lngLine As Long

For Each objVBProj In Application.VBE.VBProjects
Debug.Print "Project: " & objVBProj.Name
If objVBProj.Protection = vbext_pp_none Then
Set objVBComps = objVBProj.VBComponents
For Each objVBComp In objVBComps
Debug.Print " Component: " & objVBComp.Name
Set objVBMod = objVBComp.CodeModule
With objVBMod
lngLine = .CountOfDeclarationLines + 1
Do Until lngLine >= .CountOfLines
Debug.Print " Procedure: " & .ProcOfLine(lngLine, vbext_pk_Proc)
lngLine = lngLine + .ProcCountLines(.ProcOfLine(lngLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next objVBComp
Else
Debug.Print " is protected."
End If
Next objVBProj

End Sub

 

[Ed]

Michael:

First a few things to your posting style:
1) Don't change the subject when you continue a thread; you might
never get a response.
2) Don't top-post when the previous contributor bottom-posted
(the preferred method).
3) Remove the signature of the previous posts.
4) Snip unnecessary line from previous posts.

Sorry about the lapse in protocols.

The offending item seems to be a Class module that was given to me - it's
choking on the third procedure down, even in your amended code. It's not
that critical, so I used the Type in an If statement to bypass it. Now
everything runs fine.

I greatly appreciate your time and help with this.
Ed