A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
The following code lists the Modules and Subs in a worksheet. Note the comment re editing the sheet name for output.
The arguments for the subs will be in the sub names as listed.
Note: As well as requiring the Microsoft Visual Basic for Applications Extensibility 5.3 you also need to do the following.
On a worksheet select File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings and then check the box against Trust access to the VBA Project Object Model.
Error in comment re alternative code for processing another workbook corrected since original post. Workbook should be xlsm type; not xlsx.
Private Sub ListModulesAndSubs()
Dim wb As Workbook
Dim vbComp As VBComponent
Dim wsList As Worksheet
Dim i As Long
Dim strCodeLine As String
Dim strProcBodyLine As String
Dim strModule As String
Set wb = ThisWorkbook
'Alternative code to previous line if workbook _
being processed is not ThisWorkbook containing the code
'Set wb = Workbooks("My Other Workbook.xlsm")
Set wsList = wb.Sheets("Sheet3") 'Edit "Sheet3" to required sheet for output.
wsList.Range("A1") = "Module"
wsList.Range("B1") = "Sub Routine"
wsList.Range("A1:B1").Font.Bold = True
For Each vbComp In wb.VBProject.VBComponents
With vbComp.CodeModule
For i = .CountOfDeclarationLines + 1 To .CountOfLines
If Trim(.Lines(i, 1)) <> "" Then
If strProcBodyLine <> .Lines _
(.ProcBodyLine(.ProcOfLine(i, _
vbext_pk_Proc), vbext_pk_Proc), 1) Then
strModule = vbComp.CodeModule
strCodeLine = .Lines(i, 1)
With wsList
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) _
= strModule
.Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) _
= strCodeLine
End With
strProcBodyLine = .Lines(.ProcBodyLine(.ProcOfLine(i, _
vbext_pk_Proc), vbext_pk_Proc), 1)
End If
End If
Next i
End With
Next vbComp
wsList.Columns("A:B").Columns.AutoFit
End Sub