Chip one last question that is mildly related. When you use the code:

ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)

This returns the name of the process. Is their a way to include the
preceding word before the name (i.e. Sub, Function, Get, Let, Set) using the
existing library?

Thanks

EM

"Chip Pearson" wrote:

You report that the problem is in the call to ProcCountLines, but the
real problem is in the call to ProcOfLine. The reason is that in
ProcOfLine, the proc type variable must be a ByRef variable declared
as

Dim ProcKind As VBIDE.vbext_ProcKind

Then, you pass ProcKind to ProcOfLine. ProcOfLine then POPULATES the
ProcKind variable with the vbext_ProcKind value that indicates what
kind of proc was found at the specified line. In other words, ProcKind
is an OUTPUT variable of ProcOfLine. If you place a constant value
such as vbext_pk_Proc as the parameter to ProcOfLine, that is a ByRef
pass and there is no location for ProcOfLine to place the value of the
procedure kind of the procedure at ProcOfLine.

It works in the second case because in ProcCountLines, proc kind is an
INPUT parameter to ProcCountLines and is used to distinguish between a
Property Set, Property Get, or Property Let procedure, all of which
can have the same name.

Your code should be something like

Dim ProcKind As VBIDE.vbext_ProcKind
' ......
ProcName = .ProcOfLine(myStartLine, ProcKind) ' ProcKind is OUTPUT
NumLines = .ProcCountLines(ProcName, ProcKind) ' ProcKind is INPUT

It is acceptable to use

NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)

if you are SURE that ProcName is a Sub or Function procedure. However,
this is not the way to do it if you might have a Property procedure.

If ProcName identifies a Property procedure,

NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)

will fail because the proc kind of a Property is not equal to
vbext_pk_Proc.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 24 Sep 2009 11:16:24 -0700, ExcelMonkey
wrote:

If I have a class module and within that module I have the following line of
code:

Public Property Get Name() As String
Name = pName
End Property

In a regular module I can use the following code to extract the name of the
"sub" and pass it to the variable "ProcName"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
ModuleName = VBComp.Name
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine < .CountOfLines
SubFuncCount = SubFuncCount + 1
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
myStartLine = myStartLine + NumLines
Wend
End With
Next

?ProcName
Name

Note the use of "vbext_pk_Proc" in:
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)

However the code fails on:
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)

I know from Chip Pearson's site that:
vbext_pk_Get (3). A Property Get procedure.
vbext_pk_Let (1). A Property Let procedure.
vbext_pk_Set (2). A Property Set procedure.
vbext_pk_Proc (0). A Sub or Function procedure.

Why does vbext_pk_Proc work in the first instance but not the second?

Thanks

EM