Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
Hello,
I have a situation where 2 workbooks are open simultaneously, and I’m trying to reference a named variable (workbook scope) in the inactive workbook.. While experimenting I ran into this. Dim w As Workbook Dim s As Worksheet Set w = ThisWorkbook Set s = w.Sheets("sheet1") w.Names.Add ("Bk"), 99 s.Names.Add ("Sht"), 98 Debug.Print s.[sht] 'this works Debug.Print w.[Bk] 'this raises an error End Sub I must be missing something simple, but for the life of me, I can’t figure out why w.[bk] is raising an error. Appreciate any help on this. Regards, DaveU |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
Hello,
Just a followup to let you know I did come up with a solution. I changed w.[Bk] to Evaluate(w.Names("bk").RefersTo). For some reason, the long form works just fine. I'm still hoping someone can enlighten me. regards, DaveU |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
hi Dave,
Name is a member of the application "workbooks" not of sheets so you have to define sheet in description isabelle Le 2013-09-06 23:58, Dave Unger a écrit : Hello, Just a followup to let you know I did come up with a solution. I changed w.[Bk] to Evaluate(w.Names("bk").RefersTo). For some reason, the long form works just fine. I'm still hoping someone can enlighten me. regards, DaveU |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
oops,
Debug.Print s.[sht] 'this works Debug.Print s.[Bk] 'no more error isabelle Le 2013-09-07 00:16, isabelle a écrit : hi Dave, Name is a member of the application "workbooks" not of sheets so you have to define sheet in description isabelle Le 2013-09-06 23:58, Dave Unger a écrit : Hello, Just a followup to let you know I did come up with a solution. I changed w.[Bk] to Evaluate(w.Names("bk").RefersTo). For some reason, the long form works just fine. I'm still hoping someone can enlighten me. regards, DaveU |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
Hi Isabelle,
Thanks for the reply. Debug.Print s.[sht] 'this works Correct Debug.Print s.[Bk] 'no more error This should be Debug.Print w.[Bk] (workbook scope name) See my second post where I did this in "long hand" regards, Dave |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
what is important is Names
Worksheet.Names, property This property returns a Names collection that represents all the specific names for spreadsheets isabelle Le 2013-09-06 23:58, Dave Unger a écrit : Hello, Just a followup to let you know I did come up with a solution. I changed w.[Bk] to Evaluate(w.Names("bk").RefersTo). For some reason, the long form works just fine. I'm still hoping someone can enlighten me. regards, DaveU |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
I finally figured this one out. For some reason, you can't use the "short form" with ThisWorkbook. So in my post example, where w is set to ThisWorkbook: Debug.Print w.[Bk] will raise an error. So instead use the longer form Debug.Print Evaluate(w.names("Bk").RefersTo), this works just fine.
Regards, DaveU |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
I finally figured this one out. For some reason, you can't use the
"short form" with ThisWorkbook. So in my post example, where w is set to ThisWorkbook: Debug.Print w.[Bk] will raise an error. So instead use the longer form Debug.Print Evaluate(w.names("Bk").RefersTo), this works just fine. Regards, DaveU To get the actual 'value' held in a defined name (regardless of scope) requires using Evaluate. You can check a name's RefersTo under OERN to determine whether the name exists AND if there's a value in its RefersTo property... Dim vSetting On Error Resume Next vSetting = ActiveWorkbook.Names("DefName").RefersTo If Not (vSetting = Empty) Then vSetting = _ Application.Evaluate(ActiveWorkbook.Names("DefName ") On Error GoTo 0 ...and I use a similar approach for setting up worksheet UI settings where there's a list of values stored in local scope defined names for each sheet of a multi-sheet project. This allows me the flexibility of having differing setting for each sheet as well as providing a mechanism for toggling between what I refer to as 'UserMode' and 'DevMode' at design time... Private Const msUI_SETTINGS$ = "uiProgRows,uiProgCols,uiScrollArea,uiSelect,uiFil ter,uiOutline,uiOutlineR,uiOutlineC,uiRowColHdrs,u iProtect,uiISB,uiIsSet,uiVisible" <toggle on Sub Setup_WksUI(Optional Wks As Worksheet) Dim sz$, sWksName$, vSetting, vSettings, i% If Wks Is Nothing Then Set Wks = ActiveSheet sWksName = "'" & Wks.name & "'!" vSettings = Split(msUI_SETTINGS, ",") 'The sheet must be visible and not protected to do this Wks.Unprotect PWRD Wks.Visible = xlSheetVisible For i = LBound(vSettings) To UBound(vSettings) 'Determine if the current sheet requires the current setting vSetting = Empty On Error Resume Next If vSettings(i) = "uiScrollArea" Then Set vSetting = Application.Evaluate(sWksName & vSettings(i)) Else vSetting = Wks.Names(vSettings(i)).RefersTo If Not (vSetting = Empty) Then _ vSetting = Application.Evaluate(sWksName & vSettings(i)) End If 'vSettings(i) = "uiScrollArea" On Error GoTo 0 If Not IsEmpty(vSetting) Then Select Case vSettings(i) Case "uiProgRows": If vSetting 0 Then _ Wks.Range("A1").Resize(vSetting).EntireRow.Hidden = True Case "uiProgCols": If vSetting 0 Then _ Wks.Range("A1").Resize(, vSetting).EntireColumn.Hidden = True Case "uiScrollArea": Wks.ScrollArea = vSetting.address Case "uiSelect": Wks.EnableSelection = vSetting Case "uiFilter": Wks.EnableAutoFilter = vSetting Case "uiRowColHdrs": Wks.Activate: _ Application.ActiveWindow.DisplayHeadings = vSetting Case "uiProtect": If vSetting Then wksProtect Wks.name Case "uiVisible": Wks.Visible = vSetting Case "uiOutline": Wks.EnableOutlining = vSetting 'Persist any changes the user makes during runtime Case "uiOutlineR" If Application.Evaluate(sWksName & "uiSet") = 0 Then _ Wks.Outline.ShowLevels RowLevels:=vSetting: _ Wks.Names("uiSet").RefersTo = "=1" Case "uiOutlineC" If Application.Evaluate(sWksName & "uiSet") = 0 Then _ Wks.Outline.ShowLevels ColumnLevels:=vSetting: _ Wks.Names("uiSet").RefersTo = "=1" End Select 'Case vSettings(i) End If 'Not IsEmpty(vSetting) Next End Sub 'Setup_WksUI() <toggle off Sub Remove_WksUI(Optional Wks As Worksheet) If Wks Is Nothing Then Set Wks = ActiveSheet With Wks .Unprotect PWRD .Visible = xlSheetVisible .Activate: Application.ActiveWindow.DisplayHeadings = True .ScrollArea = "" With .UsedRange .EntireColumn.Hidden = False: .EntireRow.Hidden = False End With End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a workbook level named variable.
Hi Garry,
Thanks for your comments, I use a somewhat similar approach in my multi-sheet projects. I guess I'm a lazy typer, so for accessing sheet level defined names, I usually use the short form with the sheet qualifier, eg, Sheet1.[sVal]. I started this thread because I discovered (to my surprise) that you can't do a similar thing with a workbook level defined name, ActiveWorkbook.[wVal] does not work. Hence the "long" version, Application.Evaluate(ActiveWorkbook.names("wVal"). RefersTo). Appreciate your reply, Regards, Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set macro security level to 'low level' on opening a certain workbook. | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Named range in chart - any way to change the workbook reference with VBA | Excel Programming | |||
Concatenate a reference to named ranges in other workbook | Excel Worksheet Functions | |||
Why, when I create workbook-level name does it jump it to Sheet-level ? | Excel Programming |