Retrieving Values Saved in Names

  • Thread starter Thread starter Steve Drenker
  • Start date Start date
S

Steve Drenker

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
 
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
 
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
 
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
 
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
 
v = Evaluate(Activeworkbook.Names("MyName").Refersto)

should give you your 49.

of couse you can to

v = clng(Replace(ActiveWorkbook.Names("MyName").Refersto,"=",""))
 
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
 
i don't see any difference in what i posted

Range(nm).Value

gives the same result as

Evaluate(Activeworkbook.Names("MyName").Refersto)
 
Rob & Tom...thanks much for both techniques. Much simpler than iterating
over all 225+ names in my solution.

Steve
 
The OP said:
How do I do the same thing when I stored a value directly in the Name?
Click to expand...

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?
 
i was aware, but wasn't sure what was asked. thanks for pointing out the
difference
 
Back
Top