Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine a range with a constant number | Excel Programming | |||
range * constant | Excel Programming | |||
Multiply a Range by a Constant | Excel Programming | |||
Assigning a Range to | Excel Programming | |||
Selecting a constant changing Range | Excel Programming |