Enumerate all global variables?

J

jsp_clarke

Anyone know if there's a way to enumerate the global variables within
a workbook when opening it for automation?

I know that you can write a macro in a general module that exposes a
variable and then call that macro via:


Excel.Application.Run(MacroName)


But what if you have no control over the development of the Excel
workbook and cannot dictate that a macro be written to expose the
variable you're interested in? In fact in the case I'm dealing with I
don't even know the name of the variable. All I know is that the
workbook may have global variables of a COM component of a certain
type.


Is there some way to get a handle on all the global variables in a
workbook to enumerate through them so you can find the ones you're
interested in?
 
C

Chip Pearson

You can write VBA code that reads other VBA source code. This is outlined in
broad strokes at http://www.cpearson.com/Excel/vbe.aspx . So, technically,
yes, you can write code that enumerates the global variables in one or more
modules of another project, assuming that the VBProject isn't protected. But
knowing the variable name at run time (as opposed to compile time) won't do
you any good. There is no mechanism to get the variable's value via
automation from another project, even if that project is referenced by the
running project. CallByName is the closest you can get, conceptually, but
that doesn't work with variables.




--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top