Named Range shorthand question

[beckyinDC]

just wondering if someone could explain (and please correct me if I am misunderstanding) how Excel is behaving...

I have named ranges: [Issues_Alert] and [Total_Issues_Opened]

...why can I set up a string : "There are " & [Total_Issues_Opened] & " issues open"

...but I cannot use the same shorthand to set the other named range to that value- the following gets me an Object is needed error:

[Issues_Alert] = "There are " & [Total_Issues_Opened] & " issues open"

macro recording seems to show that I need to go through the Application.Names object to get to the Issues_Alert name to set the value...more wordy and I feel like I dont understand when I can use the shorthand and when I cant.

I can move forward with this coding using application.names-
ActiveWorkbook.names("Issues_Alert").RefersTo = "Text string"

but any tips would be appreciated -thanks!

 

[RoryA]

It appears that Issues_Alert is not a named range but a defined name that has a literal value. The Evaluate function (for which [] is a shorthand notation) returns a literal string and you then try to assign another string to it which makes no sense.

 

[beckyinDC]

Thanks Rory!- that does help me understand better- much appreciated.

 

[Jon Peltier]

Just an aside:

This illustrates why it is a bad idea to use the lazy square bracket shorthand notation. Use the complete VBA hierarchical referencing, including the defaults (equally important), and you will not encounter this type of error, and the code will be more completely self-documented.