Solved

How do I find Subs in VBA sheets/modules?

Posted on 2011-09-05
3
266 Views
Last Modified: 2012-05-12
I wish to write an Excel VA procedure that will
traverse each sheet and module returning the name of the
sheet or module and a procedure name (sub or function).
I wish to use this to self document  and to debug code that
has lots of sheets, modules, and Subs.

Ant suggestions?

PatP11
0
Comment
Question by:PatP11
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 63 total points
ID: 36484809
Chip Pearson offers code to retrieve the names of modules and subs at http://www.cpearson.com/Excel/VBE.aspx

I took his code and modified it to list all the subs in all the modules in a workbook of your choosing. It requires that you do three things:
1) Check the box to "Trust access to Visual Basic"
2) Add a reference to "Microsoft Visual Basic for Applications Extensibility 5.3"
3) Unprotect the VBA code in the target workbook

Instructions for these two steps are at the top of Pearson's web page.

The sample workbook will work if you follow Pearson's instructions.
ListSubsQ27291821.xlsm
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 62 total points
ID: 36485341
You might also want to take a look at the sample workbook/code I posted in a follow-up comment to one of my blog entries:

http://www.experts-exchange.com/blogs/matthewspatrick/B_2536-Documenting-VBA-Projects-in-Excel.html#c15769

It too is based on Chip Pearson's work, updated to include a few additional items as documented in the blog entry.
0
 

Author Closing Comment

by:PatP11
ID: 36485723
The documentation results are over whelming  Very good but not quite what I  wanted.

The answer was there, i can't access the VB compiler tables that contain sub and function names and where (objects or modules) are defined.  
Once I had access to these types of compiler structures I could do many other things interactively.

Thx for your timely support.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question