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

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
(E-Mail Removed)
"Al" <(E-Mail Removed)> wrote in message
news:A05CCC9B-3633-429E-B3E5-(E-Mail Removed)...
> 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

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
>

Then, try to explain what you ARE trying to do
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Al" <(E-Mail Removed)> wrote in message
news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
> 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
>>
>

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
> (E-Mail Removed)
> "Al" <(E-Mail Removed)> wrote in message
> news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
> > 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
> >>
> >
>
>

<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" <(E-Mail Removed)> wrote in message news:F0D166E8-33FF-461A-813C-(E-Mail Removed)...
|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
| > (E-Mail Removed)
| > "Al" <(E-Mail Removed)> wrote in message
| > news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
| > > 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
| > >>
| > >
| >
| >

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
> > (E-Mail Removed)
> > "Al" <(E-Mail Removed)> wrote in message
> > news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
> > > 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
> > >>
> > >
> >
> >

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" <(E-Mail Removed)> wrote in message news:F0D166E8-33FF-461A-813C-(E-Mail Removed)...
> |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
> | > (E-Mail Removed)
> | > "Al" <(E-Mail Removed)> wrote in message
> | > news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
> | > > 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
> | > >>
> | > >
> | >
> | >
>
>
>

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
> > > (E-Mail Removed)
> > > "Al" <(E-Mail Removed)> wrote in message
> > > news:9EA7ED73-2445-47EF-8C49-(E-Mail Removed)...
> > > > 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
> > > >>
> > > >
> > >
> > >