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?

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)








wrote in message
...
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?