Solved

How do I find Subs in VBA sheets/modules?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
location range 4 22
Macro 6 49
Excel:  Find text between 2nd hyphen and next space 10 30
Issues with DAX Calculated Columns 6 11
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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

20 Experts available now in Live!

Get 1:1 Help Now