Sub Macro1()
Dim test As Variant
Range("B9").Value = 21
Range("B9").Name = "House"
test = [house]
Debug.Print "Macro1 ", test, [house]
Macro2
End Sub

Sub Macro2()
Dim test As Variant
test = [house]
Debug.Print "Macro2 ", test, [house]
End Sub

worked fine for me.

using [namedrange] would imply the name is in the activeworkbook.

--
Regards,
Tom Ogilvy


"Ake" wrote:

Hi there,

I came across an inconsistency while coding the other day. I could easily
avoid it, so this is not the issue today. But, yet, I am curious by nature.
Is there some natural explanation, or have I come across a bug?

In its simplest form it works like this.
1. In a sheet (not in a macro) I name a cell to, say, NamedCell (say =$A$38).
2. I start a macro (singlestepping) and eventually I do
[NamedCell]="Contents", and sure enough, the contents appear in the cell
"A38".
3. As a check I do test=[NamedCell] and the contents are returned as expected
4. From within the first macro I immediately thereafter execute a second macro
5. In the second macro I immediately do test=[NamedCell], but both variables
are then declared empty. How come?

If this was a reference to a local variable, it is only proper that the
second macro does not know about it. But this is a reference to a named cell
in a sheet in a common work book- so all macros in that work book should have
access to it, I trust. (No other code is involved - not even declarations ;-)

Regards / Ake