Solved

How do I find Subs in VBA sheets/modules?

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now