Hi,
I want to create a "Library"/directory of VB code. But I'm stuck on two points (I dont know if there's already a tool for doing this so I've started to make one of my own)

The code below returns all the VB components in the active workbook. This is fine if all the modules have been named after the procedure contained in it, but it's not always practical as a single module may contain several other private or public subs or functions - one of which might be the one I'm looking for (on top of which, some modules may just be named "module1" or 2) so I really need something that returns all the individual subs in each module as well - Any suggestions?

Ideally, instead of just the active workbook, I should also be able to get a list (and the path of course) of the subs in all workbooks...but how do I access those books without opening them all and causing a memory overflow?


'This lists all VB components in a 3 column list box

Private Sub UserForm_activate()
Dim N As Integer, MyList(100, 3) 'as array type
'//headings
MyList(0, 0) = "COMPONENT NAME"
MyList(0, 1) = "COMPONENT TYPE"
MyList(0, 2) = "BOOK NAME"
'//define list
For N = 1 To ActiveWorkbook.VBProject.VBcomponents.Count
MyList(N, 0) = ActiveWorkbook.VBProject.VBcomponents(N).Name
MyList(N, 1) = ActiveWorkbook.VBProject.VBcomponents(N).Type
MyList(N, 2) = ActiveWorkbook.Name
'//change type from a number to a name
If MyList(N, 1) = 1 Then MyList(N, 1) = "Basic Module"
If MyList(N, 1) = 2 Then MyList(N, 1) = "Class Module"
If MyList(N, 1) = 3 Then MyList(N, 1) = "UserForm"
If MyList(N, 1) = 100 Then MyList(N, 1) = "Book/Sheet Class Module)"
Next N
'//load list to listbox
ListBox1.List = MyList
End Sub
'This is optional - used if a 'hard copy' list of components is wanted

Private Sub CommandButton1_Click()
Dim N As Integer
'//add a new work sheet
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
'//add headings to the sheet
Range("A1") = "COMPONENT NAME"
Range("B1") = "COMPONENT TYPE"
Range("C1") = "BOOK NAME"
'//add list to the sheet
For N = 1 To ActiveWorkbook.VBProject.VBcomponents.Count
Range("A" & N + 1) = ActiveWorkbook.VBProject.VBcomponents(N).Name
Range("B" & N + 1) = ActiveWorkbook.VBProject.VBcomponents(N).Type
Range("C" & N + 1) = ActiveWorkbook.Name
'//change type from a number to a name
If Range("B" & N + 1) = 1 Then Range("B" & N + 1) = "Basic Module"
If Range("B" & N + 1) = 2 Then Range("B" & N + 1) = "Class Module"
If Range("B" & N + 1) = 3 Then Range("B" & N + 1) = "UserForm"
If Range("B" & N + 1) = 100 Then Range("B" & N + 1) = "Book/Sheet Class Module)"
Next N
ActiveSheet.Columns("A:C").EntireColumn.AutoFit
Unload Me
End Sub