Try this, put this in a module and run "GetVbProj". This will give you a list of procedures.
Option Explicit
'Need the declaration for the following constants or
'check the reference to Microfost Visual Basic for Applications Extensbility x.xx
Const vbext_pp_none As Long = 0
Const vbext_pk_Proc As Long = 0
Dim x As Long
Dim aList()
Sub GetVbProj()
Dim oVBC As Object
Dim Wb As Workbook
x = 2
For Each Wb In Workbooks
For Each oVBC In Workbooks(Wb.Name).VBProject.VBComponents
If Workbooks(Wb.Name).VBProject.Protection = vbext_pp_none Then
Call GetCodeRoutines(Wb.Name, oVBC.Name)
End If
Next
Next
With Sheets.Add
.[a1].Resize(, 3).Value = Array("Workbook", "Module", "Procedure")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.AutoFit
End With
End Sub
Private Sub GetCodeRoutines(wbk As String, VBComp As String)
Dim VBCodeMod As Object
Dim StartLine As Long
On Error Resume Next
Set VBCodeMod = Workbooks(wbk).VBProject.VBComponents(VBComp ).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
ReDim Preserve aList(1 To 3, 1 To x - 1)
aList(1, x - 1) = wbk
aList(2, x - 1) = VBComp
aList(3, x - 1) = .ProcOfLine(StartLine, vbext_pk_Proc)
x = x + 1
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
If Err Then Exit Sub
Loop
End With
Set VBCodeMod = Nothing
End Sub
Sub List_ActiveReferences_VBAProject()
'Intentionally use of late binding but if we want to
'use early binding then we set a reference to the
'"Microsoft Visual Basic for Applications Extensibility 5.3" for 2000 and above
'For Excel 97 the name is "Microsoft Visual Basic for Applications Extensibility"
Dim oVBReference As Object
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim i As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
Application.ScreenUpdating = False
i = 1
With wsSheet
.Range("A1:F1").Value = _
Array("Description", "Name", "GUID", "Major", "Minor", "Path")
For Each oVBReference In wbBook.VBProject.References
i = i + 1
.Cells(i, 1).Value = oVBReference.Description
.Cells(i, 2).Value = oVBReference.Name
.Cells(i, 3).Value = oVBReference.GUID
.Cells(i, 4).Value = oVBReference.Major
.Cells(i, 5).Value = oVBReference.Minor
.Cells(i, 6).Value = oVBReference.FullPath
Next oVBReference
.Columns("A:F").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
Set oVBReference = Nothing
End Sub
Sub Add_External_Reference_()
' Need to Referece - Microsoft Visual Basic for Applications Extensibility 5.3
Dim rVBReference As VBIDE.Reference
Dim wbBook As Workbook
'The GUID to Microsoft Scripting Runtime.
Const stGuid As String = "{420B2830-E718-11CF-893D-00A0C9054228}"
Const stName As String = "MS Scripting Runtime"
Set wbBook = ThisWorkbook
On Error GoTo Error_Handling
With wbBook
'Iterate through the collection of active external references in
'the VB-project.
For Each rVBReference In .VBProject.References
If rVBReference.GUID = stGuid Then
MsgBox "The library of " & stName & " is already active!", _
vbInformation
GoTo ExitHere
End If
Next rVBReference
'Create the external reference in the VB-project.
.VBProject.References.AddFromGuid stGuid, 1, 0
MsgBox "The reference to " & stName & " is created!", vbInformation
GoTo ExitHere
End With
ExitHere:
Set rVBReference = Nothing
Exit Sub
Error_Handling:
MsgBox "Unable to create the reference as " & stName & vbCrLf _
& " is not available on this computer.", vbCritical
Resume ExitHere
End Sub
'Delete an active external references
Sub Delete_Reference()
' Need to Referece - Microsoft Visual Basic for Applications Extensibility 5.3
Dim wbBook As Workbook
'The easiest way is to use the description of the library.
Const stDescription As String = "Scripting"
Set wbBook = ThisWorkbook
On Error GoTo Error_Handling
With wbBook.VBProject.References
'Delete the reference.
.Remove .Item(stDescription)
End With
MsgBox "The reference is removed!", vbInformation
ExitHere:
Exit Sub
Error_Handling:
MsgBox "The reference does not exist!", vbInformation
Resume ExitHere
End Sub