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