Excel - Access a VBA global variable/constant from a worksheet cell

Asked By Lin on 28-Jul-08 04:42 PM
I want to declare several constants in VBA that should be accessible from the
worksheet cells.
Is it at all possible? I tried several combinations of GLOBAL, PUBLIC,
inside workbook/workbook_open, inside workbook/general, inside a user
module/general, inside a user module/some function. Can get the value
displayed in a MsgBox and the like, but whenever I try to assign it to a cell
I get the #NAME error. I know I could just assign it to some hidden cell in
the workbook but I thought there must be a cleaner solution...

Thanks in advance!
Lina




Mike Fogleman replied on 28-Jul-08 06:45 PM
You could make the cell value equal to the constant, but you can't access it
from the cell. What you can do is create a name for the constant with
Insert/Name/Define and access that from a cell. Create a name called glad,
and in the Refers to: box put your constant, Gladiator, for example. In your
cell type  = glad  and the word Gladiator will appear.

Mike F
Lin replied on 29-Jul-08 02:46 PM
Both suggestions were great, that solved it!
Thanks a lot!
Lina