Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.


    <pre>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
    </pre>


  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> thanks for the catch...

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank you !!!

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create list of all macros in personal workbook

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

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    The code will not work with protected code.
    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    <P ID="edit" class=small>(Edited by sdckapr on 28-May-06 10:32. Added PS)</P>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

    [pre]
    <font color=red> On Error Resume Next</font color=red>
    lPCount = BuildProcList(wbCode)
    <font color=red> If Err.Number = 50289 Then
    MsgBox Err.Description & vbCrLf & _
    "Add your code here"
    Exit Sub
    End If
    On Error GoTo 0</font color=red>
    With wbMe.Worksheets("Sheet1")

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    When the code uses :
    <pre>Worksheets("Sheet1")</pre>


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

    When the code uses:
    <pre>Worksheets(1)</pre>


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

    Steve

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

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

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •