Solved

How do I find Subs in VBA sheets/modules?

Posted on 2011-09-05
3
264 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vlookup for in-between dates 4 38
Dynamic Excel Input Form 29 32
If a cell in a range equals "YES" return specific Test. 4 30
Rather Simple Formatting Question 6 23
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 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