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

I don't see any bug. I called A1 "MyCell" then typed:

Sub test1()
[MyCell] = "contents"
test = [MyCell]
test2
End Sub

Sub test2()
test = [MyCell]
End Sub

in a code module.

Single-stepping through, *after* the line test = [MyCell] has been
executed in sub2, test holds "contents" as expected. When the line test
= [MyCell] is the highlighted line - the debugger shows you the state
of variable test *before* the line is executed. Check out test when the
End Sub is highlighted.

-John Coleman

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

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

.....it might be a bug, does it still happen if you avoid the square
brackets (...it is apparently better practice to avoid square brackets)

J

On Nov 3, 1:13 pm, 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

Maybe just the syntax. Remember that NamedCell is just a string. The
following does work:


Sub ake()
Dim test1 As String, test2 As String
Range("NamedCell").Value = "Contents"
test1 = Range("NamedCell").Address
test2 = Range("NamedCell").Value
MsgBox (test1)
MsgBox (test2)
Call ake2
End Sub

Sub ake2()
test1 = Range("NamedCell").Address
test2 = Range("NamedCell").Value
MsgBox (test1)
MsgBox (test2)
End Sub

Range("A38")
and
Range("NamedCell")

are equivalent
--
Gary's Student


"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

Yes, you got it basically right.
Except that I did the cell naming in the Excel Sheet Menu itself before any
macro was started (not in the macro).
(Not that that should make a difference ;-)

/ Ake

OK Ake, I tried it exactly as you described. Manually named the cell.
Manually entered the value. Then called the cell from the two macros. No
bug.

Sub Macro1()
Dim test As Variant
test = [Elmo]
MsgBox test
Macro2
End Sub

Sub Macro2()
Dim test As Variant
test = [Elmo]
MsgBox test
End Sub

Elmo is cell name and "Pass or Fail" is cell.value. I get Pass or Fail in
both MsgBox.

"Gary''s Student" wrote:

Maybe just the syntax. Remember that NamedCell is just a string. The
following does work:


Sub ake()
Dim test1 As String, test2 As String
Range("NamedCell").Value = "Contents"
test1 = Range("NamedCell").Address
test2 = Range("NamedCell").Value
MsgBox (test1)
MsgBox (test2)
Call ake2
End Sub

Sub ake2()
test1 = Range("NamedCell").Address
test2 = Range("NamedCell").Value
MsgBox (test1)
MsgBox (test2)
End Sub

Range("A38")
and
Range("NamedCell")

are equivalent
--
Gary's Student


"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