Retrieving Values Saved in Names
 

Hi. I need to retrieve values I've saved using Insert / Name / Define. In
other words, I've created a Name "Base_Year" with a stored value of 49. How
do I refer to this Name ("Base_Year") to get the associated value (49)

I can easily retrieve the values of named ranges on worksheets...
Dim rate As Double
rate = Range("Labor_rate_Admin_Base_Year").Value

How do I do the same thing when I stored a value directly in the Name?

I know I can iterate across all names as follows, but how do I get the
"n.RefersTo" of a specific "n.name"?

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Name
n.RefersTo
Next n

TIA, Steve

Retrieving Values Saved in Names
 

maybe this will help
in the vb editor, click view and then immediate window and then execute this
code
you should see the range name, the address of the range and the value in the
cell
if the range has multiple cells, you won't get a value

Sub name_ranges3()
on error resume next
Dim nm As Name
For Each nm In ThisWorkbook.Names
Debug.Print nm.Name
Debug.Print Range(nm).Name
Debug.Print Range(nm).Value
Next nm
End Sub

--


Gary


"Steve Drenker" wrote in message
.. .
Hi. I need to retrieve values I've saved using Insert / Name / Define. In
other words, I've created a Name "Base_Year" with a stored value of 49. How
do I refer to this Name ("Base_Year") to get the associated value (49)

I can easily retrieve the values of named ranges on worksheets...
Dim rate As Double
rate = Range("Labor_rate_Admin_Base_Year").Value

How do I do the same thing when I stored a value directly in the Name?

I know I can iterate across all names as follows, but how do I get the
"n.RefersTo" of a specific "n.name"?

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Name
n.RefersTo
Next n

TIA, Steve

Retrieving Values Saved in Names
 

Not too much help, Gary. This is what I said I knew in my original message.
I suppose I could iterate across all names looking for the known name, then
pull out the value for that name. Sure does seem like a kludge, however.
There must be a better way.

Steve

in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:00 PM:

maybe this will help
in the vb editor, click view and then immediate window and then execute this
code
you should see the range name, the address of the range and the value in the
cell
if the range has multiple cells, you won't get a value

Sub name_ranges3()
on error resume next
Dim nm As Name
For Each nm In ThisWorkbook.Names
Debug.Print nm.Name
Debug.Print Range(nm).Name
Debug.Print Range(nm).Value
Next nm
End Sub


"Steve Drenker" wrote in message
.. .
Hi. I need to retrieve values I've saved using Insert / Name / Define. In
other words, I've created a Name "Base_Year" with a stored value of 49. How
do I refer to this Name ("Base_Year") to get the associated value (49)

I can easily retrieve the values of named ranges on worksheets...
Dim rate As Double
rate = Range("Labor_rate_Admin_Base_Year").Value

How do I do the same thing when I stored a value directly in the Name?

I know I can iterate across all names as follows, but how do I get the
"n.RefersTo" of a specific "n.name"?

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Name
n.RefersTo
Next n

TIA, Steve

Retrieving Values Saved in Names
 

i use this to create code to replicate the names from one sheet to another, but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub

--


Gary


"Steve Drenker" wrote in message
.. .
Not too much help, Gary. This is what I said I knew in my original message.
I suppose I could iterate across all names looking for the known name, then
pull out the value for that name. Sure does seem like a kludge, however.
There must be a better way.

Steve

in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:00 PM:

maybe this will help
in the vb editor, click view and then immediate window and then execute this
code
you should see the range name, the address of the range and the value in the
cell
if the range has multiple cells, you won't get a value

Sub name_ranges3()
on error resume next
Dim nm As Name
For Each nm In ThisWorkbook.Names
Debug.Print nm.Name
Debug.Print Range(nm).Name
Debug.Print Range(nm).Value
Next nm
End Sub


"Steve Drenker" wrote in message
.. .
Hi. I need to retrieve values I've saved using Insert / Name / Define. In
other words, I've created a Name "Base_Year" with a stored value of 49. How
do I refer to this Name ("Base_Year") to get the associated value (49)

I can easily retrieve the values of named ranges on worksheets...
Dim rate As Double
rate = Range("Labor_rate_Admin_Base_Year").Value

How do I do the same thing when I stored a value directly in the Name?

I know I can iterate across all names as follows, but how do I get the
"n.RefersTo" of a specific "n.name"?

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Name
n.RefersTo
Next n

TIA, Steve

Retrieving Values Saved in Names
 

I just put this together. You pass the function the name of the stored
constant. It returns the value of the stored constant. Still seems like a
kludge to me, but it works. I must be missing something here.

Function GetConst(ConstName As String) As String
Dim n As Name

For Each n In ActiveWorkbook.Names
If n.Name = ConstName Then
GetConst = Mid(n.RefersTo, 2) ' Strips off leading "="
Exit Function
End If
Next n
End Function

Sub TestGetConst()
Dim str As String

Debug.Print GetConst("Base_Year")
End Sub



in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:36 PM:

i use this to create code to replicate the names from one sheet to another,
but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub

Retrieving Values Saved in Names
 

v = Evaluate(Activeworkbook.Names("MyName").Refersto)

should give you your 49.

of couse you can to

v = clng(Replace(ActiveWorkbook.Names("MyName").Refers to,"=",""))



--
Regards,
Tom Ogivy

"Steve Drenker" wrote in message
.. .
I just put this together. You pass the function the name of the stored
constant. It returns the value of the stored constant. Still seems like a
kludge to me, but it works. I must be missing something here.

Function GetConst(ConstName As String) As String
Dim n As Name

For Each n In ActiveWorkbook.Names
If n.Name = ConstName Then
GetConst = Mid(n.RefersTo, 2) ' Strips off leading "="
Exit Function
End If
Next n
End Function

Sub TestGetConst()
Dim str As String

Debug.Print GetConst("Base_Year")
End Sub



in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:36 PM:

i use this to create code to replicate the names from one sheet to
another,
but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub

Retrieving Values Saved in Names
 

Hi Steve,

If you're trying to get the name from within the same workbook where
it's defined then the following will work for a workbook-level name:

Dim lYear As Long
lYear = Application.Evaluate("Base_Year")

for a sheet-level name you'll need to specify the worksheet it belongs to:

lYear = Application.Evaluate("MySheet!Base_Year")

To retrieve the name from a different workbook you should fully qualify the
location of the name, like so:

lYear = Application.Evaluate("[Book1.xls]MySheet!Base_Year")


--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Steve Drenker" wrote in message
.. .
Hi. I need to retrieve values I've saved using Insert / Name / Define. In
other words, I've created a Name "Base_Year" with a stored value of 49.
How
do I refer to this Name ("Base_Year") to get the associated value (49)

I can easily retrieve the values of named ranges on worksheets...
Dim rate As Double
rate = Range("Labor_rate_Admin_Base_Year").Value

How do I do the same thing when I stored a value directly in the Name?

I know I can iterate across all names as follows, but how do I get the
"n.RefersTo" of a specific "n.name"?

Dim n As Name

For Each n In ActiveWorkbook.Names
n.Name
n.RefersTo
Next n

TIA, Steve

Retrieving Values Saved in Names
 

i don't see any difference in what i posted

Range(nm).Value

gives the same result as

Evaluate(Activeworkbook.Names("MyName").Refersto)


--


Gary


"Tom Ogilvy" wrote in message
...
v = Evaluate(Activeworkbook.Names("MyName").Refersto)

should give you your 49.

of couse you can to

v = clng(Replace(ActiveWorkbook.Names("MyName").Refers to,"=",""))



--
Regards,
Tom Ogivy

"Steve Drenker" wrote in message
.. .
I just put this together. You pass the function the name of the stored
constant. It returns the value of the stored constant. Still seems like a
kludge to me, but it works. I must be missing something here.

Function GetConst(ConstName As String) As String
Dim n As Name

For Each n In ActiveWorkbook.Names
If n.Name = ConstName Then
GetConst = Mid(n.RefersTo, 2) ' Strips off leading "="
Exit Function
End If
Next n
End Function

Sub TestGetConst()
Dim str As String

Debug.Print GetConst("Base_Year")
End Sub



in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:36 PM:

i use this to create code to replicate the names from one sheet to
another,
but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub

Retrieving Values Saved in Names
 

Rob & Tom...thanks much for both techniques. Much simpler than iterating
over all 225+ names in my solution.

Steve

in article , Rob Bovey at
wrote on 3/3/06 6:58 PM:

Hi Steve,

If you're trying to get the name from within the same workbook where
it's defined then the following will work for a workbook-level name:

Dim lYear As Long
lYear = Application.Evaluate("Base_Year")

for a sheet-level name you'll need to specify the worksheet it belongs to:

lYear = Application.Evaluate("MySheet!Base_Year")

To retrieve the name from a different workbook you should fully qualify the
location of the name, like so:

lYear = Application.Evaluate("[Book1.xls]MySheet!Base_Year")

Retrieving Values Saved in Names
 

The OP said:

How do I do the same thing when I stored a value directly in the Name?

Maybe you aren't aware you can do this, but is isn't a range - it is a
value.

Let's look in the immediate window:

Names.Add Name:="ABCD", Refersto:="=49"
? Names("ABCD").RefersTo
=49
nm = "ABCD"
? range(nm).Value

**** Big 1004 Error *****

? Evaluate(Activeworkbook.Names(nm).Refersto)
49

See the difference?

--
Regards,
Tom Ogilvyl




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i don't see any difference in what i posted

Range(nm).Value

gives the same result as

Evaluate(Activeworkbook.Names("MyName").Refersto)


--


Gary


"Tom Ogilvy" wrote in message
...
v = Evaluate(Activeworkbook.Names("MyName").Refersto)

should give you your 49.

of couse you can to

v = clng(Replace(ActiveWorkbook.Names("MyName").Refers to,"=",""))



--
Regards,
Tom Ogivy

"Steve Drenker" wrote in message
.. .
I just put this together. You pass the function the name of the stored
constant. It returns the value of the stored constant. Still seems like
a
kludge to me, but it works. I must be missing something here.

Function GetConst(ConstName As String) As String
Dim n As Name

For Each n In ActiveWorkbook.Names
If n.Name = ConstName Then
GetConst = Mid(n.RefersTo, 2) ' Strips off leading "="
Exit Function
End If
Next n
End Function

Sub TestGetConst()
Dim str As String

Debug.Print GetConst("Base_Year")
End Sub



in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:36 PM:

i use this to create code to replicate the names from one sheet to
another,
but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub

Retrieving Values Saved in Names
 

i was aware, but wasn't sure what was asked. thanks for pointing out the
difference

--


Gary


"Tom Ogilvy" wrote in message
...
The OP said:

How do I do the same thing when I stored a value directly in the Name?

Maybe you aren't aware you can do this, but is isn't a range - it is a
value.

Let's look in the immediate window:

Names.Add Name:="ABCD", Refersto:="=49"
? Names("ABCD").RefersTo
=49
nm = "ABCD"
? range(nm).Value

**** Big 1004 Error *****

? Evaluate(Activeworkbook.Names(nm).Refersto)
49

See the difference?

--
Regards,
Tom Ogilvyl




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i don't see any difference in what i posted

Range(nm).Value

gives the same result as

Evaluate(Activeworkbook.Names("MyName").Refersto)


--


Gary


"Tom Ogilvy" wrote in message
...
v = Evaluate(Activeworkbook.Names("MyName").Refersto)

should give you your 49.

of couse you can to

v = clng(Replace(ActiveWorkbook.Names("MyName").Refers to,"=",""))



--
Regards,
Tom Ogivy

"Steve Drenker" wrote in message
.. .
I just put this together. You pass the function the name of the stored
constant. It returns the value of the stored constant. Still seems like
a
kludge to me, but it works. I must be missing something here.

Function GetConst(ConstName As String) As String
Dim n As Name

For Each n In ActiveWorkbook.Names
If n.Name = ConstName Then
GetConst = Mid(n.RefersTo, 2) ' Strips off leading "="
Exit Function
End If
Next n
End Function

Sub TestGetConst()
Dim str As String

Debug.Print GetConst("Base_Year")
End Sub



in article , Gary Keramidas at
GKeramidasATmsn.com wrote on 3/3/06 6:36 PM:

i use this to create code to replicate the names from one sheet to
another,
but
i'm not sure what you want

Sub test5() ' use this one to update ranges
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & sName &
Right(nm.Name, Len(nm.Name) - 3) & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
'& """ & """ = """ & Range(nm).Address & ""
Next nm
End Sub