Assigning a constant to a range name
 

In XL2003 on XP, I want to assign a constant to a range name, but it's not
working the way I expect.
My test case:

Sub test()
Dim i As Single
i = 0.05 'result of some procedure
' Obviously this works
ActiveWorkbook.Names.Add Name:="Rate", RefersToR1C1:="=Names!R1C1"
Range("A1") = i
MsgBox Range("Rate")
' This assigns the value and I can use it on the worksheet, but why can't I
read it back?
ActiveWorkbook.Names.Add Name:="IntRate", RefersToR1C1:=i
MsgBox Range("IntRate")
End Sub

Can someone explain this?
--
Al C

Assigning a constant to a range name
 

IF??? you want to name cell a1 then

Sub namerng()
Range("a1").Name = "i"
'Range("i").Select
MsgBox Range("i")
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Al" wrote in message
...
In XL2003 on XP, I want to assign a constant to a range name, but it's not
working the way I expect.
My test case:

Sub test()
Dim i As Single
i = 0.05 'result of some procedure
' Obviously this works
ActiveWorkbook.Names.Add Name:="Rate", RefersToR1C1:="=Names!R1C1"
Range("A1") = i
MsgBox Range("Rate")
' This assigns the value and I can use it on the worksheet, but why can't
I
read it back?
ActiveWorkbook.Names.Add Name:="IntRate", RefersToR1C1:=i
MsgBox Range("IntRate")
End Sub

Can someone explain this?
--
Al C

Assigning a constant to a range name
 

I'm trying not to name a cell at all, and I'm curious as to why it doesn't
work in my macro. It can certainly be done on a worksheet.
--
Al C


"Don Guillett" wrote:

IF??? you want to name cell a1 then

Assigning a constant to a range name
 

Then, try to explain what you ARE trying to do
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Al" wrote in message
...
I'm trying not to name a cell at all, and I'm curious as to why it doesn't
work in my macro. It can certainly be done on a worksheet.
--
Al C


"Don Guillett" wrote:

IF??? you want to name cell a1 then

Assigning a constant to a range name
 

I want to pass a value from my macro to a users worksheet, and I can
accomplish that as in the first half of my test or in your response. My
question is simply that I want to learn why what I did in the second half of
my test doesn't return the value I assigned.
--
Al C


"Don Guillett" wrote:

Then, try to explain what you ARE trying to do
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Al" wrote in message
...
I'm trying not to name a cell at all, and I'm curious as to why it doesn't
work in my macro. It can certainly be done on a worksheet.
--
Al C


"Don Guillett" wrote:

IF??? you want to name cell a1 then

Assigning a constant to a range name
 

<doesn't return the value I assigned

You didn't assign a value. You tried to Insert a defined name, but didn't refer to a correct cell or range reference. You referred
to i instead, which is not a reference, but contains the value 0.05

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Al" wrote in message ...
|I want to pass a value from my macro to a users worksheet, and I can
| accomplish that as in the first half of my test or in your response. My
| question is simply that I want to learn why what I did in the second half of
| my test doesn't return the value I assigned.
| --
| Al C
|
|
| "Don Guillett" wrote:
|
| Then, try to explain what you ARE trying to do
| --
| Don Guillett
| Microsoft MVP Excel
| SalesAid Software
|
| "Al" wrote in message
| ...
| I'm trying not to name a cell at all, and I'm curious as to why it doesn't
| work in my macro. It can certainly be done on a worksheet.
| --
| Al C
|
|
| "Don Guillett" wrote:
|
| IF??? you want to name cell a1 then
|
|
|
|

Assigning a constant to a range name
 

I think you have created a name, but not a named range. Therefore, you can't
use Range(...) to refer to it. Try instead

MsgBox ActiveWorkbook.Names("IntRate").Value

Hope this helps,

Hutch

"Al" wrote:

I want to pass a value from my macro to a users worksheet, and I can
accomplish that as in the first half of my test or in your response. My
question is simply that I want to learn why what I did in the second half of
my test doesn't return the value I assigned.
--
Al C


"Don Guillett" wrote:

Then, try to explain what you ARE trying to do
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Al" wrote in message
...
I'm trying not to name a cell at all, and I'm curious as to why it doesn't
work in my macro. It can certainly be done on a worksheet.
--
Al C


"Don Guillett" wrote:

IF??? you want to name cell a1 then

Assigning a constant to a range name
 

Now I'm getting confused. After I execute my macro, I go to the worksheet
and the name "IntRate" refers to the value 0.05 and I can use it like any
other named value. I understand it's not a named range. Am I using the
wrong terms for what I'm describing? Whatever it is, is there a way to
access it from some macro, since it is associated with the workbook to which
I assigned it?
--
Al C


"Niek Otten" wrote:

<doesn't return the value I assigned

You didn't assign a value. You tried to Insert a defined name, but didn't refer to a correct cell or range reference. You referred
to i instead, which is not a reference, but contains the value 0.05

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Al" wrote in message ...
|I want to pass a value from my macro to a users worksheet, and I can
| accomplish that as in the first half of my test or in your response. My
| question is simply that I want to learn why what I did in the second half of
| my test doesn't return the value I assigned.
| --
| Al C
|
|
| "Don Guillett" wrote:
|
| Then, try to explain what you ARE trying to do
| --
| Don Guillett
| Microsoft MVP Excel
| SalesAid Software
|
| "Al" wrote in message
| ...
| I'm trying not to name a cell at all, and I'm curious as to why it doesn't
| work in my macro. It can certainly be done on a worksheet.
| --
| Al C
|
|
| "Don Guillett" wrote:
|
| IF??? you want to name cell a1 then
|
|
|
|

Assigning a constant to a range name
 

Hutch

Thank you! You hit the nail on the head.
--
Al C


"Tom Hutchins" wrote:

I think you have created a name, but not a named range. Therefore, you can't
use Range(...) to refer to it. Try instead

MsgBox ActiveWorkbook.Names("IntRate").Value

Hope this helps,

Hutch

"Al" wrote:

I want to pass a value from my macro to a users worksheet, and I can
accomplish that as in the first half of my test or in your response. My
question is simply that I want to learn why what I did in the second half of
my test doesn't return the value I assigned.
--
Al C


"Don Guillett" wrote:

Then, try to explain what you ARE trying to do
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Al" wrote in message
...
I'm trying not to name a cell at all, and I'm curious as to why it doesn't
work in my macro. It can certainly be done on a worksheet.
--
Al C


"Don Guillett" wrote:

IF??? you want to name cell a1 then