Enumerating the names of *subs* and *functions*

D

David Schrader

Hello all,

I've searched through the Excel "Help" structure, the MS KB
and Excel pages as well as google-ing for all the combinations
I could think of to find a method to produce a list of all of the
user *sub*s and *function*s which are contained in an Excel
file without success. (Somehow, I haven't been able to reach
a point where MS's "key word" list make logical or rational
sense to me when I make a search.)

Here's what I want to do--
- I want to scan all of the modules in an Excel spreadsheet
and create an enumeration of their names (and types - sub
or function).
- Then, if a names meets certain conditions I want to execute
that *sub* or *function* using a "for each" loop. Basically,
For each [SUB or Function]
If [Sub or Function] = Criteria Then
Execute [Sub or Function] Name
Else
' Do nothing
End If
Next ' [Sub or Function] name
I know that there must be some method of doing it and can
probably figure out how *IF* I can find out the correct
terminology/methodology. Anyone have any pointers to
where I can find the information?

David
 
B

Bernie Deitrick

David,

The code below will give you a starting point - you will need to reference MS VBE extesibility for
the code to work.

Note that you cannot RUN a function - you can call it, but it should return a value. So, for help
modifying this code, you will need to explain your criteria and what you expect from "running"
functions.

HTH,
Bernie
MS Excel MVP

Sub ListFunctionsAndSubs2()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

On Error Resume Next

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Module Type"
Cells(1, 4).Value = "Procedure Name"
Cells(1, 5).Value = "Type"
Cells(1, 6).Value = "Start Line"
Cells(1, 7).Value = "Number of Lines"

RowNdx = 2
Set myBook = ActiveWorkbook
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Or VBComp.Type = vbext_ct_ClassModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = IIf(VBComp.Type = vbext_ct_ClassModule, "Class Mod", "Std
Mod")
Cells(RowNdx, 4).Value = ProcName
If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else: myType = "SubRoutine"
End If
Cells(RowNdx, 5).Value = myType
Cells(RowNdx, 6).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 7).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp

End Sub


David Schrader said:
Hello all,

I've searched through the Excel "Help" structure, the MS KB
and Excel pages as well as google-ing for all the combinations
I could think of to find a method to produce a list of all of the
user *sub*s and *function*s which are contained in an Excel
file without success. (Somehow, I haven't been able to reach
a point where MS's "key word" list make logical or rational
sense to me when I make a search.)

Here's what I want to do--
- I want to scan all of the modules in an Excel spreadsheet
and create an enumeration of their names (and types - sub
or function).
- Then, if a names meets certain conditions I want to execute
that *sub* or *function* using a "for each" loop. Basically,
For each [SUB or Function]
If [Sub or Function] = Criteria Then
Execute [Sub or Function] Name
Else
' Do nothing
End If
Next ' [Sub or Function] name
I know that there must be some method of doing it and can
probably figure out how *IF* I can find out the correct
terminology/methodology. Anyone have any pointers to
where I can find the information?

David
Click to expand...
 
D

David Schrader

Bernie,

Thanks for the start. At least now I can get a start and
have a command name I can search for... . :eek:)

In point of fact I am aware that a function *must* return
a value - I merely indicated it since there may be a case
(or two) where I would need to execute (a better word
perhaps) one or the other within the "For Each" loop. If
a function was called it would definitely be executed in a
manner which accepted the data/information returned by
the function as in the example which follows my signature.

David

For each [SUB or Function]
Select Case [Sub or Function]
Case [Sub]
Execute [Sub]
Case [Function]
Variable[for specific Function] = [Function (args.)]
:
Else
' Do nothing
End Select
Next ' [Sub or Function] name

Bernie Deitrick said:
David,

The code below will give you a starting point - you will need to reference
MS VBE extesibility for the code to work.

Note that you cannot RUN a function - you can call it, but it should
return a value. So, for help modifying this code, you will need to
explain your criteria and what you expect from "running" functions.

HTH,
Bernie
MS Excel MVP

Sub ListFunctionsAndSubs2()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

On Error Resume Next

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Module Type"
Cells(1, 4).Value = "Procedure Name"
Cells(1, 5).Value = "Type"
Cells(1, 6).Value = "Start Line"
Cells(1, 7).Value = "Number of Lines"

RowNdx = 2
Set myBook = ActiveWorkbook
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Or VBComp.Type =
vbext_ct_ClassModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = IIf(VBComp.Type =
vbext_ct_ClassModule, "Class Mod", "Std Mod")
Cells(RowNdx, 4).Value = ProcName
If .Find("Fun", myStartLine, 1, myStartLine, 100, False,
False, False) Then
myType = "Function"
Else: myType = "SubRoutine"
End If
Cells(RowNdx, 5).Value = myType
Cells(RowNdx, 6).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 7).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp

End Sub


David Schrader said:
Hello all,

I've searched through the Excel "Help" structure, the MS KB
and Excel pages as well as google-ing for all the combinations
I could think of to find a method to produce a list of all of the
user *sub*s and *function*s which are contained in an Excel
file without success. (Somehow, I haven't been able to reach
a point where MS's "key word" list make logical or rational
sense to me when I make a search.)

Here's what I want to do--
- I want to scan all of the modules in an Excel spreadsheet
and create an enumeration of their names (and types - sub
or function).
- Then, if a names meets certain conditions I want to execute
that *sub* or *function* using a "for each" loop. Basically,
For each [SUB or Function]
If [Sub or Function] = Criteria Then
Execute [Sub or Function] Name
Else
' Do nothing
End If
Next ' [Sub or Function] name
I know that there must be some method of doing it and can
probably figure out how *IF* I can find out the correct
terminology/methodology. Anyone have any pointers to
where I can find the information?

David
Click to expand...
Click to expand...
 
B

Bernie Deitrick

This code will execute the sub msghello (note the extea sub at the bottom of my message) and any
other sub that has the three letters "msg" (case sensitive) in its name.

HTH,
Bernie
MS Excel MVP


Sub ListFunctionsAndSubs3()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

On Error Resume Next

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Module Type"
Cells(1, 4).Value = "Procedure Name"
Cells(1, 5).Value = "Type"
Cells(1, 6).Value = "Start Line"
Cells(1, 7).Value = "Number of Lines"

RowNdx = 2
Set myBook = ActiveWorkbook
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Or VBComp.Type = vbext_ct_ClassModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = IIf(VBComp.Type = vbext_ct_ClassModule, "Class Mod", "Std
Mod")
Cells(RowNdx, 4).Value = ProcName
If InStr(1, ProcName, "msg") > 0 Then
Application.Run myBook.Name & "!" & ProcName
End If
If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else: myType = "SubRoutine"
End If
Cells(RowNdx, 5).Value = myType
Cells(RowNdx, 6).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 7).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp

End Sub

Sub msghello()
MsgBox "hello, world"
End Sub


David Schrader said:
Bernie,

Thanks for the start. At least now I can get a start and
have a command name I can search for... . :eek:)

In point of fact I am aware that a function *must* return
a value - I merely indicated it since there may be a case
(or two) where I would need to execute (a better word
perhaps) one or the other within the "For Each" loop. If
a function was called it would definitely be executed in a
manner which accepted the data/information returned by
the function as in the example which follows my signature.

David

For each [SUB or Function]
Select Case [Sub or Function]
Case [Sub]
Execute [Sub]
Case [Function]
Variable[for specific Function] = [Function (args.)]
:
Else
' Do nothing
End Select
Next ' [Sub or Function] name

Bernie Deitrick said:
David,

The code below will give you a starting point - you will need to reference MS VBE extesibility
for the code to work.

Note that you cannot RUN a function - you can call it, but it should return a value. So, for
help modifying this code, you will need to explain your criteria and what you expect from
"running" functions.

HTH,
Bernie
MS Excel MVP

Sub ListFunctionsAndSubs2()

Dim myBook As Workbook

Dim myStartLine As Long
Dim NumLines As Long

Dim ProcName As String
Dim VBComp As VBComponent
Dim RowNdx As Long

On Error Resume Next

Cells(1, 1).Value = "Workbook Name"
Cells(1, 2).Value = "Module Name"
Cells(1, 3).Value = "Module Type"
Cells(1, 4).Value = "Procedure Name"
Cells(1, 5).Value = "Type"
Cells(1, 6).Value = "Start Line"
Cells(1, 7).Value = "Number of Lines"

RowNdx = 2
Set myBook = ActiveWorkbook
For Each VBComp In myBook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Or VBComp.Type = vbext_ct_ClassModule Then
NumLines = 0
With VBComp.CodeModule
myStartLine = .CountOfDeclarationLines + 1
While myStartLine + NumLines < .CountOfLines
ProcName = .ProcOfLine(myStartLine, vbext_pk_Proc)
Cells(RowNdx, 1).Value = myBook.Name
Cells(RowNdx, 2).Value = VBComp.Name
Cells(RowNdx, 3).Value = IIf(VBComp.Type = vbext_ct_ClassModule, "Class Mod", "Std
Mod")
Cells(RowNdx, 4).Value = ProcName
If .Find("Fun", myStartLine, 1, myStartLine, 100, False, False, False) Then
myType = "Function"
Else: myType = "SubRoutine"
End If
Cells(RowNdx, 5).Value = myType
Cells(RowNdx, 6).Value = myStartLine
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
Cells(RowNdx, 7).Value = NumLines
myStartLine = myStartLine + NumLines
RowNdx = RowNdx + 1
Wend
End With
End If

Next VBComp

End Sub


David Schrader said:
Hello all,

I've searched through the Excel "Help" structure, the MS KB
and Excel pages as well as google-ing for all the combinations
I could think of to find a method to produce a list of all of the
user *sub*s and *function*s which are contained in an Excel
file without success. (Somehow, I haven't been able to reach
a point where MS's "key word" list make logical or rational
sense to me when I make a search.)

Here's what I want to do--
- I want to scan all of the modules in an Excel spreadsheet
and create an enumeration of their names (and types - sub
or function).
- Then, if a names meets certain conditions I want to execute
that *sub* or *function* using a "for each" loop. Basically,
For each [SUB or Function]
If [Sub or Function] = Criteria Then
Execute [Sub or Function] Name
Else
' Do nothing
End If
Next ' [Sub or Function] name
I know that there must be some method of doing it and can
probably figure out how *IF* I can find out the correct
terminology/methodology. Anyone have any pointers to
where I can find the information?

David
Click to expand...
Click to expand...
Click to expand...
 
A

Alan Beban

David said:
Bernie,. . .
In point of fact I am aware that a function *must* return
a value . . . .
Click to expand...

In fact, a function need *not* return a value.

Alan Beban
 
B

Bernie Deitrick

In fact, a function need *not* return a value.
Click to expand...

Alan,

I suppose a function could change the value of a global or ByRef variable, but why would I want to
code it as a function rather than as a sub if there is no returned value? Personally, I think I
would use a sub and not a function in that case... just for future ease of understanding my code.

Bernie
 
A

Alan Beban

Bernie said:
Alan,

I suppose a function could change the value of a global or ByRef variable, but why would I want to
code it as a function rather than as a sub if there is no returned value? Personally, I think I
would use a sub and not a function in that case... just for future ease of understanding my code.

Bernie
Click to expand...
I don't disagree; I was only responding to the universal statement that
a function *must* return a value because that might be misleading,
especially to an inexperienced user.

The functions I use for manipulating ByRef variables do, in fact, have a
return value of True or False to indicate success or failure, so that
the calling code can error trap to insure that the change was
successfully accomplished.

Regards,
Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WORD VBA Search and switch to advanced ... 1
Need help modifying code 0
VBA - exporting serial letters to single documents 0
How to call module function or sub from the Add In? 3
Drop down list control value in vba 2
Opening a list of Excel files and run a macro in each 17
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
How to count cells with same fill colour containing same text...JoeMoe 0

Top