Create list of all macros in personal workbook (Excel 2000 SR1)

Topic

New Reply

Is there a way to create a list of all of my Modules and macros in those modules that are in my personal.xls file?

Reply | Quote

Replies

Here’s some quick and dirty code I cobbled together some time ago – there are probably tidier ways of doing this. I’m not sure, but the original source would probably have been either here, j-walk or chip (but all errors are mine entirely!) This works on the active workbook only.

Sub ListFuncsAndProcs()

Dim aComp
Dim WkBkComps
Set WkBkComps = ActiveWorkbook.VBProject.VBComponents
Dim RowVal As Integer, colval As Integer
Application.DisplayAlerts = False
On Error Resume Next 
Sheets("list of funcs and procs").Delete
On Error GoTo 0 
Application.DisplayAlerts = True

Sheets.Add.Name = "list of funcs and procs"
ActiveWindow.Zoom = 50
colval = 1

Cells(1, colval).Value = ".Name"
Cells(2, colval).Value = ".Type"
Cells(3, colval).Value = ".codemodule"
Cells(4, colval).Value = ".count of lines"

ActiveSheet.UsedRange.EntireColumn.AutoFit
colval = 2
Dim intCol As Integer

On Error Resume Next 
    For Each aComp In WkBkComps
        Cells(1, colval).Value = aComp.Name
        Cells(2, colval).Value = aComp.Type
        Cells(3, colval).Value = aComp.CodeModule
        intCol = aComp.CodeModule.CountOfLines
        Cells(4, colval).Value = intCol
Dim x
RowVal = 5
Dim strtemp
            For x = 1 To intCol
            strtemp = aComp.CodeModule.Lines(x, 1)
If Left(strtemp, 8) = "Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 16) = "Private Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 15) = "Public Function" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 3) = "Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 11) = "Private Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
If Left(strtemp, 10) = "Public Sub" Then
Cells(RowVal, colval).Value = strtemp: RowVal = RowVal + 1: End If
            Next
        colval = colval + 1
    Next
End Sub

Reply | Quote

Just an additional remark: for Brooke’s code to run, you must set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library in Tools | References… (in the Visual Basic Editor)

Reply | Quote

thanks for the catch…

Reply | Quote

Thank you !!!

Reply | Quote

Attached is a workbook that can be used to get a list of the modules and code in a workbook. It also produces either a .rtf or .txt file that contains a listing of the procedures and functions. The .rtf file can be used to print a formatted listing of all of the code in the workbook. This only works for standard modules, not class modules. To use the workbook, open the workbook and click on the PrintWBCode button. Select the options you want in the dialog box and click OK. Then find the workbook you want to print the code from, select it and click on the Open button. You should get a list of the modules, the procedure/function names, along with some information about them. Select a name and location for the .rtf or .txt file and click on OK. You can then save the workbook with the module and procedure list.

Reply | Quote

An excellent piece of code – just what I was looking for!

Reply | Quote

Legare,

I tried your code and seems useful. But it crashed with an error saying “Programmatic access to Visual Basic Project is not trusted.”

I have a number of add-ins in my Excel environment that are password-protected. I also have an add-in that is not so protected. I created a trivial workbook with a macro built by the recorder for testing.

The crash came in your sub BuildProcList in about the first executable line (For each ocomponent …).

I wasn’t sure if the protected add-ins were the culprit but haven’t unloaded them to test.

Fred

Reply | Quote

The code will not work with protected code.

Reply | Quote

Fred: Two problems: code on VBA does not work on protected projects (but it does if you manually open the project and enter the PW before running the code) AND you need to change a security setting: Tools, macro, security, trusted sources tab, allow access to visual basic projects.

Reply | Quote

Jan Karel and Legare,

I understand about the protected projects. It would seem that many people might have such projects – for example, I have 2 protected projects, one of which is the analysis toolpack for VBA as an add-in (atpvbaen). I’d think the analysis toolpack is another. But I would not have the passwords.

I am more than happy to forego seeing the listing of modules and macros in such projects. However, it would seem useful to be able to see modules and macros for unprotected projects even though there are other protected projects in one’s environment. For example for things that I write. It does not seem useful to have to unload protected projects to be able to see a listing and then add them back in.

As an experiment, I have a unprotected project called Conversions for converting all kinds of units that I got off the lounge some time ago. I changed the name of it to aaConversions so it came first in the list of projects. Then I ran Legare’s macro. It stopped for the same reason. I tried to check if any info had been gathered but couldn’t find any. I also don’t know if the name change really had any effect as far as Legare’s macro goes – the crash occurs in the loop for each component of projects so it’s unclear if renaming things changed the order in which the loop checks projects.

So is there a way to find that a project is protected and bypass that one? Even if so, I would not bother to prompt for a password. Perhaps just include that project’s name in the output and indicate it’s protected?

Fred

Reply | Quote

(Edited by sdckapr on 28-May-06 10:32. Added PS)

I am confused. Legare’s code works on 1 file and its code must be unprotected or you get and error. Even if other workbooks are open with protected projects, you do not get the error. You only get the error when you try to read workbooks that are project protected.

If you are going thru a list of workbooks, you could trap the error, do something else, then resume. But how you do it depends on what your code is doing.

Steve
PS for example in Legare’s “PrintWBCode” subroutine, you could add the red lines to the existing code to indicate the problem and do something else

On Error Resume Next
lPCount = BuildProcList(wbCode)
If Err.Number = 50289 Then
MsgBox Err.Description & vbCrLf & _
“Add your code here”
Exit Sub
End If
On Error GoTo 0
With wbMe.Worksheets(“Sheet1”)

Reply | Quote

Steve,

You’re right about the number of files being accessed. I’ve solved the problem. Actually I had 2 issues:
– I didn’t have the box checked for allowing access to VBA projects since I thought I read somewhere that isn’t a good practice. This solved the problem I originally posted.

– I had saved Legare’s original post (not the attachment) as part of my downloaded copy of his workbook. I put the text of the post in Sheet 2 and renamed it to “Lounge Post”. Then I moved this sheet to be the first sheet in the workbook. Even though there was a Sheet1 named Sheet1, it appears that the ordering of worksheets is important. I saw in the code that there was a reference to Sheet1. Even tho the VBA env shows his original Sheet1 as being in the first position regardless of the ordering of the sheets in the workbook, the ordering does seem to make a difference.

Fred

Reply | Quote

When the code uses :

Worksheets("Sheet1")

It is referring to a sheet literally named “Sheet1” and is independent of the order of the sheets.

When the code uses:

Worksheets(1)

It is referring to the first worksheet in the workbook and the name is unimportant, it is solely based on the order.

Steve

Reply | Quote

Yep – I checked the code and there is one sub that uses Worksheets(1). Others use Worksheets(“Sheet1”).

Fred

Reply | Quote