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

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

Interesting. I saw an example like this on your site and could not figure
out for the life of me where ProcKind was getting its value from as it did
not look as though it was being assigned anywhere. Good to know.

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

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

I found some code in my standard library that you might find useful:


' Requires a reference to:
' Name: VBIDE
' Description: Microsoft Visual Basic for Applications
Extensibility 5.3
' GUID: {0002E157-0000-0000-C000-000000000046}
' Major: 5 Minor: 3
' Typical Location: C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB

Public Type TDeclarationInfo
Success As Boolean
ProcName As String
ProcType As String
ProcKind As VBIDE.vbext_ProcKind
Scope As String
ProcBodyLine As Long
ProcStartLine As Long
ProcEndLine As Long
ProcCountLines As Long
Declaration As String
NormalizedDeclaration As String
End Type

Function ProcInfoFromLine(CodeMod As VBIDE.CodeModule, LineNumber As
Long) As TDeclarationInfo

Dim SS As Variant
Dim N As Long
Dim DI As TDeclarationInfo
Dim S As String
Dim T As String


With CodeMod
If LineNumber <= .CountOfDeclarationLines Then
DI.Success = False
ProcInfoFromLine = DI
Exit Function
End If
DI.ProcName = .ProcOfLine(LineNumber, DI.ProcKind)
DI.ProcStartLine = .ProcStartLine(DI.ProcName, DI.ProcKind)
DI.ProcBodyLine = .ProcBodyLine(DI.ProcName, DI.ProcKind)
DI.ProcCountLines = .ProcCountLines(DI.ProcName, DI.ProcKind)
DI.ProcEndLine = DI.ProcStartLine + DI.ProcCountLines - 1
N = 0
S = .Lines(DI.ProcBodyLine + N, 1)
Do Until StrComp(Right(S, 2), " _", vbBinaryCompare) < 0
N = N + 1
T = .Lines(DI.ProcBodyLine + N, 1)
S = S & vbNewLine & T
Loop
DI.Declaration = S
T = S
T = Replace(T, vbNewLine, Space(1))
T = Replace(T, " _ ", Space(1))
N = InStr(1, T, Space(2))
Do Until N = 0
T = Replace(T, Space(2), Space(1))
N = InStr(1, T, Space(2))
Loop
DI.NormalizedDeclaration = T
SS = Split(DI.NormalizedDeclaration, Space(1))
Select Case DI.ProcKind
Case VBIDE.vbext_pk_Get
DI.ProcType = "Property Get"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Let
DI.ProcType = "Property Let"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Set
DI.ProcType = "Property Set"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Proc

Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
DI.ProcType = SS(1)
Case Else
DI.Scope = "default"
DI.ProcType = SS(0)
End Select
End Select
DI.Success = True
End With
ProcInfoFromLine = DI
End Function

The ProcInfoFromLine function takes as input a reference to a
VBIDE.CodeModule and a LineNumber and loads and returns a
TDeclarationInfo structure with many properties of the procedure that
contains LineNumber in CodeMod.

The elements of the TDeclarationInfo structure are for the most part
self-explanatory. ProcType is a string contain the type of procedure,
eg., "Sub", "Property Get", etc. ProcKind is the vbext_ProcKind value
of the procedure. Scope is "public", "private","friend", or "default
(no scope specified)". Declaration is the complete declaration of the
procedure, including line continuation characters and line breaks, and
padding space if the declaration spans more than one line in the code.
NormalizeDeclaration is the declaration with all lines breaks, and
line continuation characters stripped out, and then it is single
spaced (multiple spaces convert to single spaces).

Once you have the code above, you would call it with code like


Sub AAATest()
Dim VBP As VBIDE.VBProject
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim ModuleName As String
Dim DI As TDeclarationInfo

'<<< CHANGE THE NEXT TWO LINES AS DESIRED
ModuleName = "Module1"
LineNum = 20
'<<< END CHANGE
Set VBP = Application.VBE.ActiveVBProject
Set CodeMod = VBP.VBComponents(ModuleName).CodeModule
DI = ProcInfoFromLine(CodeMod, LineNum)
If DI.Success = False Then
Debug.Print "error"
Else
With DI
Debug.Print "---------------------"
Debug.Print "PROC:", .ProcName
Debug.Print "---------------------"
Debug.Print "Scope", .Scope
Debug.Print "ProcType", .ProcType
Debug.Print "ProcKind", .ProcKind
Debug.Print "ProcStart", .ProcStartLine
Debug.Print "ProcBodyLine", .ProcBodyLine
Debug.Print "ProcCountLines", .ProcCountLines
Debug.Print "ProcEndLine", .ProcEndLine
Debug.Print "NormalizedDeclaration", .NormalizedDeclaration
Debug.Print "Declaration", .Declaration
Debug.Print "---------------------"
End With
End If
End Sub


All this code comes for a project I've been working on to build a
complete VBA code management and control system. The management
project is written in VBNET 2008, NET 3.5.1 and, if all goes well, can
be used in any Office application and using an SQL Server Express
database.

I hope you find the code helpful, or at least amusing.

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 12:14:13 -0700, ExcelMonkey
wrote:

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

Yes very helpful. It sure pulls you deep into the minutae. So much to
consder......

Thanks again.

EM

"Chip Pearson" wrote:

I found some code in my standard library that you might find useful:


' Requires a reference to:
' Name: VBIDE
' Description: Microsoft Visual Basic for Applications
Extensibility 5.3
' GUID: {0002E157-0000-0000-C000-000000000046}
' Major: 5 Minor: 3
' Typical Location: C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB

Public Type TDeclarationInfo
Success As Boolean
ProcName As String
ProcType As String
ProcKind As VBIDE.vbext_ProcKind
Scope As String
ProcBodyLine As Long
ProcStartLine As Long
ProcEndLine As Long
ProcCountLines As Long
Declaration As String
NormalizedDeclaration As String
End Type

Function ProcInfoFromLine(CodeMod As VBIDE.CodeModule, LineNumber As
Long) As TDeclarationInfo

Dim SS As Variant
Dim N As Long
Dim DI As TDeclarationInfo
Dim S As String
Dim T As String


With CodeMod
If LineNumber <= .CountOfDeclarationLines Then
DI.Success = False
ProcInfoFromLine = DI
Exit Function
End If
DI.ProcName = .ProcOfLine(LineNumber, DI.ProcKind)
DI.ProcStartLine = .ProcStartLine(DI.ProcName, DI.ProcKind)
DI.ProcBodyLine = .ProcBodyLine(DI.ProcName, DI.ProcKind)
DI.ProcCountLines = .ProcCountLines(DI.ProcName, DI.ProcKind)
DI.ProcEndLine = DI.ProcStartLine + DI.ProcCountLines - 1
N = 0
S = .Lines(DI.ProcBodyLine + N, 1)
Do Until StrComp(Right(S, 2), " _", vbBinaryCompare) < 0
N = N + 1
T = .Lines(DI.ProcBodyLine + N, 1)
S = S & vbNewLine & T
Loop
DI.Declaration = S
T = S
T = Replace(T, vbNewLine, Space(1))
T = Replace(T, " _ ", Space(1))
N = InStr(1, T, Space(2))
Do Until N = 0
T = Replace(T, Space(2), Space(1))
N = InStr(1, T, Space(2))
Loop
DI.NormalizedDeclaration = T
SS = Split(DI.NormalizedDeclaration, Space(1))
Select Case DI.ProcKind
Case VBIDE.vbext_pk_Get
DI.ProcType = "Property Get"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Let
DI.ProcType = "Property Let"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Set
DI.ProcType = "Property Set"
Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
Case Else
DI.Scope = "default"
End Select
Case VBIDE.vbext_pk_Proc

Select Case LCase(SS(0))
Case "public", "private", "friend"
DI.Scope = SS(0)
DI.ProcType = SS(1)
Case Else
DI.Scope = "default"
DI.ProcType = SS(0)
End Select
End Select
DI.Success = True
End With
ProcInfoFromLine = DI
End Function

The ProcInfoFromLine function takes as input a reference to a
VBIDE.CodeModule and a LineNumber and loads and returns a
TDeclarationInfo structure with many properties of the procedure that
contains LineNumber in CodeMod.

The elements of the TDeclarationInfo structure are for the most part
self-explanatory. ProcType is a string contain the type of procedure,
eg., "Sub", "Property Get", etc. ProcKind is the vbext_ProcKind value
of the procedure. Scope is "public", "private","friend", or "default
(no scope specified)". Declaration is the complete declaration of the
procedure, including line continuation characters and line breaks, and
padding space if the declaration spans more than one line in the code.
NormalizeDeclaration is the declaration with all lines breaks, and
line continuation characters stripped out, and then it is single
spaced (multiple spaces convert to single spaces).

Once you have the code above, you would call it with code like


Sub AAATest()
Dim VBP As VBIDE.VBProject
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim ModuleName As String
Dim DI As TDeclarationInfo

'<<< CHANGE THE NEXT TWO LINES AS DESIRED
ModuleName = "Module1"
LineNum = 20
'<<< END CHANGE
Set VBP = Application.VBE.ActiveVBProject
Set CodeMod = VBP.VBComponents(ModuleName).CodeModule
DI = ProcInfoFromLine(CodeMod, LineNum)
If DI.Success = False Then
Debug.Print "error"
Else
With DI
Debug.Print "---------------------"
Debug.Print "PROC:", .ProcName
Debug.Print "---------------------"
Debug.Print "Scope", .Scope
Debug.Print "ProcType", .ProcType
Debug.Print "ProcKind", .ProcKind
Debug.Print "ProcStart", .ProcStartLine
Debug.Print "ProcBodyLine", .ProcBodyLine
Debug.Print "ProcCountLines", .ProcCountLines
Debug.Print "ProcEndLine", .ProcEndLine
Debug.Print "NormalizedDeclaration", .NormalizedDeclaration
Debug.Print "Declaration", .Declaration
Debug.Print "---------------------"
End With
End If
End Sub


All this code comes for a project I've been working on to build a
complete VBA code management and control system. The management
project is written in VBNET 2008, NET 3.5.1 and, if all goes well, can
be used in any Office application and using an SQL Server Express
database.

I hope you find the code helpful, or at least amusing.

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 12:14:13 -0700, ExcelMonkey
wrote:

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