+ Reply to Thread
Results 1 to 4 of 4

Excel VBA or any free tool for list all VBA function procedure and Sub Procedure

  1. #1
    Registered User
    Join Date
    07-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    41

    Excel VBA or any free tool for list all VBA function procedure and Sub Procedure

    Hi all,
    I am in the stage of tidy up all my UDF and and Sub Procedure I wrote since several years ago.
    As of now those excel files scatter in many folders and sub folders.
    What I am looking for now is excel VBA with ability to loop through folder and all its sub folders and create a list of
    " folder name, sub folder name, excel file name, and all UDF and and Sub Procedure contain on the file".
    Or if there are any free tools for this purpose would be nice.

    Regards,
    Benny

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Excel VBA or any free tool for list all VBA function procedure and Sub Procedure

    Have a look at this link http://cpearson.com/excel/vbe.aspx
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Excel VBA or any free tool for list all VBA function procedure and Sub Procedure

    Try this in attached workbook
    1. Amend myFolder path
    2. Run SubFolderList - creates list of files with extension = "xlsm" on sheet "FileList"
    3. Run ListProceduresByType - opens every file in sheet "FileList" and lists all procedures in sheet "List"

    Notes
    - if files are password protected, you will be asked for the password
    - cannot suppress "Do you want to Update Links" (answer message with "No")
    - old lists are cleared each time

    Modify to suit your own requirements

    Original code to drill down to subfolders written by Rich on StackOverflow.com
    Please Login or Register  to view this content.

    Original code found on MrExcel.com by CraigM used to extract list of functions has been modified below to loop through multiple procedure "types" and multiple workbooks.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Excel VBA or any free tool for list all VBA function procedure and Sub Procedure

    Hi Kev
    Thank you for link and ListVBAModulesV1.xlsm.
    Sorry for late on reply your answer.
    After doing some search I end up with put some additional stuff on your original ListVBAModulesV1.xlsm‎.
    Below is the list of additional I got so far:
    1. Create 2 command button on filelist sheet
    01.JPG
    2. Add input box with password mask (this part was originaly written by Daniel Klann, with assumption that all protected workbook have the same password)
    02.JPG
    3. Add progress bar( The progress bar originally written by Ejaz Ahmed)
    03.JPG
    4. add ability to copy "list" sheet to a new sheet with different name as soon as ListProceduresByType complete the job. During step 4 there are some additional sub program created:
    4.1 ability to cut proposed sheet name if exceed 31 characters.
    4.2 ability to put an index at the end of file name (suffix) in case the name already exist.
    4.3 ability to scan if proposed name contain invalid characters for sheet name.

    I attached the file and feel free to test it and let me know if you find a better way other then my approach.



    Best Regards,
    Benny
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] call variable from one procedure to in other procedure
    By shiva_raj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2017, 02:19 AM
  2. [SOLVED] Exit Sub Procedure when called on Sub Procedure Exits
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2015, 08:17 AM
  3. Calling Procedure or Function inside another Procedure - variables problem
    By Rudo123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2015, 03:12 PM
  4. Procedure skips portion of code when called from another procedure
    By abkar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-26-2014, 05:14 PM
  5. [SOLVED] How to use second procedure (the 2nd Sub) results in first procedure in the same module
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2014, 10:17 AM
  6. [SOLVED] Create a time delay at end of procedure that will then call on next procedure
    By Clay Shooter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2013, 05:56 AM
  7. Replies: 0
    Last Post: 10-02-2012, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1