Sub M3CleanMods()
'Remove any Sub not listed below!
'Standard Module code, like: Module3.
'Note: You will need to create a reference to MS VBA Extensibilty 5.3
'(in VBE go to Tools>References) to work this code, it was set in this version!
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long, Line1 As Long, Lines As Long
Dim myList1 As String, myList2 As String, myCode As String
'Get modules!
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
'Option: To limit Sub removal to this type of Module [If]!
'If VBComp.Type = vbext_ct_StdModule Then
'Get Sub's name [VBCodeMod]!
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
'List Sub's name [myCode]!
myCode = .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
'Build all Sub's list [myList1]!
myList1 = myList1 + " " + vbCr + VBCodeMod + ": " & myCode
'Do not remove these Sub's!
'For best operation each Sub's name should be unique!
If myCode = "M1Test1" Then GoTo myLoop
If myCode = "S1Test1" Then GoTo myLoop
If myCode = "M2deMods" Then GoTo myLoop
If myCode = "M2Remov" Then GoTo myLoop
If myCode = "TWmySh3" Then GoTo myLoop
If myCode = "M3CleanMods" Then GoTo myLoop
'Build deleted sub's list [myList2]!
myList2 = myList2 + " " + vbCr + VBCodeMod + ": " & myCode
'Delete correct Sub!
With VBCodeMod
Line1 = .ProcStartLine(myCode, vbext_pk_Proc)
Lines = .ProcCountLines(myCode, vbext_pk_Proc)
.DeleteLines Line1, Lines
End With
myLoop:
Loop
End With
'Option: To limit Sub removal to this type of Module [End]!
'End If
Next VBComp
'Show results!
MsgBox "These Components have been found:" & vbCr & myList1 & vbCr & _
vbCr & vbCr & "These Components have been deleted:" & vbCr & myList2
End Sub