Need the first line of the VBA code of all my UDF

[SeniorNewbie]

Hi! out there,

with this little snippet I'm able to list all userdefined functions to a worksheet:

Option Explicit

Public Sub ListProcedures()
Dim oMod As Object
Dim i As Integer, iStart As Integer

Cells.Clear

i = 1
For Each oMod In ActiveWorkbook.VBProject.VBComponents
    With oMod.CodeModule
        If oMod.Name = "mdlFunctions" Then
            iStart = .CountOfDeclarationLines + 1
            Do Until iStart >= .CountOfLines
                Cells(i, 1) = .ProcOfLine(iStart, 0)
                Cells(i, 2) = oMod.Type & "*"
                Cells(i, 3) = oMod.Name
                iStart = iStart + .ProcCountLines(.ProcOfLine(iStart, 0), 0)
                i = i + 1
            Loop
        End If
    End With
Next

End Sub

As you'll understand I'll get the name and the type of the procedure. What I need is the entire first line of the code e.g. Public Function KissMe(Sound as a String, Duration as Long, Taste as Byte)

I guess that it's only one line more in my code, but so far I'm helpless.

Thank you very much and have a nice weekend!

Newbie sr.


EDIT:
btw &fyi: Types are listed as:
100 = code in a sheet
1 = subs AND functions
3 = events in a userform

 

[Domenic]

Try the following...

Option Explicit

Public Sub ListProcedures()
Dim oMod As Object
Dim i As Long, j As Long, iStart As Long

Cells.Clear

i = 1
For Each oMod In ActiveWorkbook.VBProject.VBComponents
    With oMod.CodeModule
        If oMod.Name = "mdlFunctions" Then
            iStart = .CountOfDeclarationLines + 1
            Do Until iStart >= .CountOfLines
                Cells(i, 1).Value = .ProcOfLine(iStart, 0)
                Cells(i, 2).Value = oMod.Type & "*"
                Cells(i, 3).Value = oMod.Name
                j = iStart
                Do While Len(.Lines(j, 1)) = 0
                    j = j + 1
                Loop
                Cells(i, 4).Value = .Lines(j, 1)
                iStart = iStart + .ProcCountLines(.ProcOfLine(iStart, 0), 0)
                i = i + 1
            Loop
        End If
    End With
Next

End Sub

Hope this helps!

 

[SeniorNewbie]

Try the following...

veni, vidi vici!!

Domenic - you're hero of my day! THX a lot and enjoy the weekend!

Newbie sr.

 

[Domenic]

You're most welcome, I'm glad I could help.

Cheers!