dj44
06-08-2017, 04:27 PM
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
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