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

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" wrote in message
...
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

Bernie,

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

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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
Bernie,

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

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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

David Schrader wrote:
Bernie,. . .
In point of fact I am aware that a function *must* return
a value . . . .

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

David,

. . .
Note that you cannot RUN a function - you can call it, but it should
return a value.

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

Alan Beban

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

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

Bernie Deitrick wrote:
In fact, a function need *not* return a value.


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


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