Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Set macro security level to 'low level' on opening a certain workbook. andreashermle Excel Programming 2 November 15th 10 07:33 AM
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... christian_spaceman Excel Programming 3 December 24th 07 02:15 PM
Named range in chart - any way to change the workbook reference with VBA Graham Whitehead Excel Programming 1 October 23rd 07 03:02 PM
Concatenate a reference to named ranges in other workbook sbardon Excel Worksheet Functions 4 October 15th 06 01:58 AM
Why, when I create workbook-level name does it jump it to Sheet-level ? Charles Jordan Excel Programming 1 November 5th 03 09:43 PM


All times are GMT +1. The time now is 09:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"