square bracket range designation

[RobBroekhuis]

In a recent post, Acron used a syntax for range designation that I hadn't seen before:
[Recipients]
instead of
range("Recipients&quot

Works like a charm, even though I can't seem to find an official reference to that syntax. I tried it right away in some of my code, and came across an oddity: I'm working on an active sheet ("summary&quot, while a different sheet in the same workbook ("info&quot has some named ranges (single cells, in this case) which contain general values I need to reference in my program.
Two of those values are: "stepRx1" and "tRx1"
They are defined exactly analogously (no difference obvious in the name-define form), but somehow they behave differently. When I break my code while executing,
?[tRx1] gives me the proper value, and ?[tRx1].address works fine too.
?[stepRx1] gives me 0 instead of its real value (11), and
?[stepRx1].address throws an error.
Any ideas on what may be causing the discrepancy?
The old syntax range("stepRx1&quot works fine (but I like the square-bracket syntax better).
Rob

 

[RobBroekhuis]

More info: the fact that the ranges are not on the active sheet doesn't seem to matter - when I change the active sheet to "info", I get the same behavior. When I try
?[xxxx]
where xxxx is undefined (i.e., not a valid range name), I get "Error 2029". So VBA recognizes stepRx1 as a range name (I don't get the error corresponding to an unknown reference), but doesn't get the addressing right (?)
Rob

 

[RobBroekhuis]

Okay, so the square brackets mean "evaluate", per Acron's explanation in a different thread. Here is my latest try at making sense of my problem (all in the debug window):

?evaluate("trx1&quot
5.83084493055556E-03
?evaluate("stepRx1&quot
11
?[stepRx1]
0

I'm getting more and more puzzled. A bug, a feature, a corrupted workbook?
Rob

 

[acron]

Rob,

Just tried you examples, but cannot seem to replicate the problem. [stepRx1] works fine for me. Perhaps there is some some conflict in your setup, though I did try using th ename on 2 different sheeets without problem.

Would Sheets(X).[stepRx1].address work ?, (where X is the index number of the sheet).

A.C

 

[acron]

< all in the debug window >.

Have you tried it in a module sub ?

 

[RobBroekhuis]

Acron,
Yes, it does work when I specify the sheet (but why should I have to - it's the active sheet!). Example:

?activesheet.name
info
?[stepRx1]
0
?evaluate(&quot;stepRx1&quot
11
?sheets(&quot;info&quot.[stepRx1]
11

I just deleted and redefined stepRx1, and that didn't help. I'd rather use
range(&quot;stepRx1&quot
(my old way of doing things) than
sheets(&quot;info&quot.[stepRx1]
so am I back to zero?
It's not a big deal, but I can't stand it when things that should work don't...
Rob

 

[RobBroekhuis]

Ah - another clue. Trying to answer your question about executing from a sub, I went to make one - and prompty realized I was still in debug mode. Exited debug mode, and all of a sudden the commands from the debug screen worked:
?[stepRx1]
11

I executed the code again, up to my breakpoint, and the same thing happened: [stepRx1] is again undefined (zero).
I still don't understand...
Rob