List to Immediate Window - Worksheet Code Subs Procedures

[dj44]

folks,

good day again

i was very happy to learn how to output the sub procedure names in a module to the immediate window :checkmark

But I just cant access the worksheet code

I have a lot of subs in my worksheet, and now i wanted to know what they are

the below works only for NORMAL modules




Sub Worksheet_Subs()

' Show in the immediate window all the subs in the worksheet named DataList

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind

Set VBProj = ActiveWorkbook.VBProject



'Set VBComp = ActiveWorkbook.VBProject.VBComponents("DataList").CodeModule ' << Worksheet - Where i have my private subs
'Set CodeMod = VBComp.CodeModule



Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule



With CodeMod


LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, ProcKind)


Debug.Print ProcName '<< Show in the Immediate window a list of Sub names in the module YES WORKS

LineNum = .ProcStartLine(ProcName, ProcKind) + _
.ProcCountLines(ProcName, ProcKind) + 1



Loop
End With
End Sub




what is missing from the above, i tried everything that intelisence showed me

thank you for any advice

[Bob Phillips]

Sub Worksheet_Subs()
' Show in the immediate window all the subs in the worksheet named DataList

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents

Debug.Print VBComp.Name
With VBComp.CodeModule

LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, ProcKind)


Debug.Print vbTab & ProcName '<< Show in the Immediate window a list of Sub names in the module YES WORKS

LineNum = .ProcStartLine(ProcName, ProcKind) + _
.ProcCountLines(ProcName, ProcKind) + 1
Loop
End With
Next VBComp
End Sub

[dj44]

Hello distinguished Lord of the VBA

Thank you very much for fixing this up

Now I'm no Michael Jackson and I'm sure that I couldn't do a moon dance to save my life but heres a happy dance:dance:
I'm very happy because I've been trying to do this job for a week now


One of my most treasured macros got hidden in a worksheet and I couldn't find it because I forgot the name and the work sheet got deleted and I was very upset at losing it.

But now I can just quickly see where all my sub macros are and copy and paste the names and where they are
and then later on I can make a checklist of what I need to back up

thank you very much it made a nice list for me



cheers all and forum and happy early weekend


:beerchug: