Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieving stored names of drop down lists | New Users to Excel | |||
Retrieving Names of Worksheets | Excel Programming | |||
Retrieving previously entered userform data from saved spreadsheet | Excel Programming | |||
Retrieving the sheet names of another workbook | Excel Programming | |||
retrieving table names from Excel and Access sources using ADO | Excel Programming |