Reference a workbook level named variable.

[Dave Unger]

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

 

[Dave Unger]

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

 

[isabelle]

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 :

 

[isabelle]

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

 

[Dave Unger]

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

 

[isabelle]

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 :

 

[Dave Unger]

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

 

[GS]

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,uiFilter,uiOutline,uiOutlineR,uiOutlineC,uiRowColHdrs,uiProtect,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

 

[Dave Unger]

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