Named Constant

[Shatin]

Hi All,

If I have a named constant in a certain worksheet called "Status" whose
inital value is "On". What's the vba code for:

- finding out what the value of this named constant is?
- changing the value to "Off" if the value is "On"?

TIA

 

[Ron Coderre]

Try this:

To get the value:
Names("Status").RefersTo

To set the value:
Names("Status").RefersTo = "OFF"

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jim Thomlinson]

A constant cannot be changed. That is the whole point to a constant. The
value remains constant no matter what. As a guess you are looking for a
global variable???

So instead of
Const MyVar as String = "On"
you want
Public MyVar as String

By default the string will be empty. You will need to initialize it prior to
using it.

 

[Shatin]

Wow, that's quick and exactly what I want!

Many thanks indeed, Ron!

 

[Jim Cone]

On the other hand...
If "Status" is truly a String Constant then its value will not change.
That is what "constant" means.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Shatin"
wrote in message
Hi All,
If I have a named constant in a certain worksheet called "Status" whose
inital value is "On". What's the vba code for:
- finding out what the value of this named constant is?
- changing the value to "Off" if the value is "On"?
TIA

 

[Ron Coderre]

You're very welcome, Shatin......I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)