Referencing Named Formula in VBA

[Mark Worthington]

I am familiar with referencing Named Ranges in VBA, namely Set x =
Range("x"), where x=Sheet1!$A$1

However, I have defined a Named Constant in a worksheet, x=2. I have
=x in cell $A$1 (giving 2), but if I try to reference this in code
using the above Set statement, I get the very helpful error message :

Run-time Error '1004':

Application-defined or Object-defined error

If I then define a "normal" Named Range, y=Sheet1!$A$1, then Set x =
Range("y") works as expected.

Am I am trying something odd? A Named Constant or Range is really a
Named Formula, which all exist in Excel's memory, so I don't know why
one works while the other does not.

Any help would be much appreciated!

Mark

 

[Charles Williams]

2 is not a reference to a range so Excel cannot handle it

if you want the result of the name just use [x] or Application.evaluate("x")

dim dValue as double

dValue=[x]

(if x was a name defined as =Sheet1!$A$1 then dvalue would give you the
value in Sheet1!$A$1)


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Dave Peterson]

How about something like:

MsgBox Evaluate(ThisWorkbook.Names("x").RefersTo)

 

[Mark Worthington]

Charles,

Thanks for that!

It would appear to me that accessing the worksheet name directly using
[Name] rather then my normal method

dim Name as Range
Set Name = Range("Name")

in my VBA would be OK, and save creating a VBA variable.

I'm amost inclined to say, why bother with defining VBA variables for
any worksheet name?!?!

Am I missing something, or is it just down to personal
preference......

Thanks,

Mark