• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

How do I find Subs in VBA sheets/modules?

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
PatP11
Asked:
PatP11
2 Solutions
 
byundtCommented:
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
 
Patrick MatthewsCommented:
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
 
PatP11Author Commented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now