When I create a named range through the Name Manager, I'm given the option of specifying Workbook or [worksheet name] scope. But if then want to change scope, the drop-down is grayed out. Is there a way, in either Name Manager or, preferablly, VBA to change the scope of an existing named range?
For example:
testName refers to 'sheet1'!A1:B2 with scope Workbook. How would I change that totestName refers to 'sheet1'!A1:B2 with 'sheet1' scope?You can download the free Name Manager addin developed by myself and Jan Karel Pieterse from http://www.decisionmodels.com/downloads.htm This enables many name operations that the Excel 2007 Name manager cannot handle, including changing scope of names.
In VBA:
Sub TestName()
Application.Calculation = xlManual
Names("TestName").Delete
Range("Sheet1!$A$1:$B$2").Name = "Sheet1!TestName"
Application.Calculation = xlAutomatic
End Sub
Check out these two subs that reverse each other and flip the scope (worksheet to workbook or reverse) of all named ranges that refer to a range on the active sheet.
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : RescopeNamedRangesToWorkbook
' Author : JS20'07'11
' Date : 11/18/2013
' Purpose : Rescopes the parent of worksheet scoped named ranges to the active workbook
' for each named range with a scope equal to the active sheet in the active workbook.
'---------------------------------------------------------------------------------------
Public Sub RescopeNamedRangesToWorkbook()
Dim wb As Workbook
Dim ws As Worksheet
Dim objName As Name
Dim sWsName As String
Dim sWbName As String
Dim sRefersTo As String
Dim sObjName As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet
sWsName = ws.Name
sWbName = wb.Name
'Loop through names in worksheet.
For Each objName In ws.Names
'Check name is visble.
If objName.Visible = True Then
'Check name refers to a range on the active sheet.
If InStr(1, objName.RefersTo, sWsName, vbTextCompare) Then
sRefersTo = objName.RefersTo
sObjName = objName.Name
'Check name is scoped to the worksheet.
If objName.Parent.Name <> sWbName Then
'Delete the current name scoped to worksheet replacing with workbook scoped name.
sObjName = Mid(sObjName, InStr(1, sObjName, "!") + 1, Len(sObjName))
objName.Delete
wb.Names.Add Name:=sObjName, RefersTo:=sRefersTo
End If
End If
End If
Next objName
End Sub
'---------------------------------------------------------------------------------------
' Procedure : RescopeNamedRangesToWorksheet
' Author : JS20'07'11
' Date : 11/18/2013
' Purpose : Rescopes each workbook scoped named range to the specific worksheet to
' which the range refers for each named range that refers to the active worksheet.
'---------------------------------------------------------------------------------------
Public Sub RescopeNamedRangesToWorksheet()
Dim wb As Workbook
Dim ws As Worksheet
Dim objName As Name
Dim sWsName As String
Dim sWbName As String
Dim sRefersTo As String
Dim sObjName As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet
sWsName = ws.Name
sWbName = wb.Name
'Loop through names in worksheet.
For Each objName In wb.Names
'Check name is visble.
If objName.Visible = True Then
'Check name refers to a range on the active sheet.
If InStr(1, objName.RefersTo, sWsName, vbTextCompare) Then
sRefersTo = objName.RefersTo
sObjName = objName.Name
'Check name is scoped to the workbook.
If objName.Parent.Name = sWbName Then
'Delete the current name scoped to workbook replacing with worksheet scoped name.
objName.Delete
ws.Names.Add Name:=sObjName, RefersTo:=sRefersTo
End If
End If
End If
Next objName
End Sub
An alternative way is to "hack" the Excel file for 2007 or higher, although it is advisable to take care if you are doing this, and keep a backup of the original:
First save the Excel spreadsheet as an .xlsx or .xlsm file (not binary). rename the file to .zip, then unzip. Go to the xl folder in the zip structure and open workbook.xml in Wordpad or a similar text editor. Named ranges are found in the definedName tags. Local scoping is defined by localSheetId="x" (the sheet IDs can be found by pressing Alt-F11 in Excel, with the spreadsheet open, to get to the VBA window, and then looking at the Project pane). Hidden ranges are defined by hidden="1", so just delete the hidden="1" to unhide, for example.
Now rezip the folder structure, taking care to maintain the integrity of the folder structure, and rename back to .xlsx or .xlsm.
This is probably not the best solution if you need to change the scope of or hide/unhide a large number of defined ranges, though it works fine for making one or two small tweaks.
These answers were helpful in solving a similar issue while trying to define a named range with Workbook scope. The "ah-HA!" for me is to use the Names Collection which is relative to the whole Workbook! This may be restating the obvious to many, but it wasn't clearly stated in my research, so I share for other's with similar questions.
' Local / Worksheet only scope
Worksheets("Sheet2").Names.Add Name:="a_test_rng1", RefersTo:=Range("A1:A4")
' Global / Workbook scope
ThisWorkbook.Names.Add Name:="a_test_rng2", RefersTo:=Range("B1:b4")
If you look at your list of names when Sheet2 is active, both ranges are there, but switch to any other sheet, and "a_test_rng1" is not present.
Now I can happily generate a named range in my code with what ever scope I deem appropriate. No need mess around with the name manager or a plug in.
Aside, the name manager in Excel Mac 2011 is a mess, but I did discover that while there are no column labels to tell you what you're looking at while viewing your list of named ranges, if there is a sheet listed beside the name, that name is scoped to worksheet / local. See screenshot attached.
Full credit to this article for putting together the pieces.
here's how I promote all worksheet names to global names. YMMV
For Each wsh In ActiveWorkbook.Worksheets
For Each n In wsh.Names
' Get unqualified range name
Dim s As String
s = Split(n.Name, "!")(UBound(Split(n.Name, "!")))
' Add to "Workbook" scope
n.RefersToRange.Name = s
' Remove from "Worksheet" scope
Call n.Delete
Next n
Next wsh
This still needs more refining however works with all simple references, without killing existing local names.
Type GlobalNamesToLocalNames_Type
Name As String
Sheet As String
Ref As String
End Type
Sub GlobalNamesToLocalNames(Optional Void As Variant)
Dim List() As GlobalNamesToLocalNames_Type
Dim Count As Long
Dim Name As Name
Dim Dat() As String
Dim X As Long
' count the size
For Each Name In ActiveWorkbook.Names
Count = Count + 1
Next
ReDim List(Count - 1)
Count = 0
' Collecect all name data
For Each Name In ActiveWorkbook.Names
With List(Count)
' Pick up only the name
If InStr(Name.Name, "!") > 0 Then
Dat = Split(Name.Name, "!")
.Name = Dat(1)
Else
.Name = Name.Name
End If
' pick up the sheet and refer
Dat = Split(Name.RefersTo, "!")
.Sheet = Mid(Dat(0), 2)
.Ref = Dat(1)
' make local sheet name
.Name = .Sheet & "!" & .Name
End With
Count = Count + 1
Next
' Delete all names
For Each Name In ActiveWorkbook.Names
Name.Delete
Next
'rebuild all the names
For X = 0 To Count - 1
With List(X)
If Left(.Ref, 1) <> "#" Then
ActiveWorkbook.Names.Add Name:=.Name, RefersToLocal:="=" & .Sheet & "!" & .Ref
End If
End With
Next
End Sub
The code of JS20'07'11 is really incredible simple and direct. One suggestion that I would like to give is to put a exclamation mark in the conditions:
InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare)
Because this will prevent adding a NamedRange in an incorrect Sheet. Eg: If the NamedRange refers to a Sheet named Plan11 and you have another Sheet named Plan1 the code can do some mess when add the ranges if you don't use the exclamation mark.
UPDATE
A correction: It's best to use a regular expression evaluate the name of the Sheet. A simple function that you can use is the following (adapted by http://blog.malcolmp.com/2010/regular-expressions-excel-add-in, enable Microsoft VBScript Regular Expressions 5.5):
Function xMatch(pattern As String, searchText As String, Optional matchIndex As Integer = 1, Optional ignoreCase As Boolean = True) As String
On Error Resume Next
Dim RegEx As New RegExp
RegEx.Global = True
RegEx.MultiLine = True
RegEx.pattern = pattern
RegEx.ignoreCase = ignoreCase
Dim matches As MatchCollection
Set matches = RegEx.Execute(searchText)
Dim i As Integer
i = 1
For Each Match In matches
If i = matchIndex Then
xMatch = Match.Value
End If
i = i + 1
Next
End Function
So, You can use something like that:
xMatch("'?" +sWsName + "'?" + "!", objName.RefersTo, 1) <> ""
instead of
InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare)
This will cover Plan1 and 'Plan1' (when the range refers to more than one cell) variations
TIP: Avoid Sheet names with single quotes ('), :) .
I added some additional lines of code to JS20'07'11's previous Makro to make sure that the name of the sheet's Named Ranges isn't already a name of the workbook's Named Ranges. Without these lines the already definied workbook scooped Named range is deleted and replaced.
Public Sub RescopeNamedRangesToWorkbookV2()
Dim wb As Workbook
Dim ws As Worksheet
Dim objNameWs As Name
Dim objNameWb As Name
Dim sWsName As String
Dim sWbName As String
Dim sRefersTo As String
Dim sObjName As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet
sWsName = ws.Name
sWbName = wb.Name
'Loop through names in worksheet.
For Each objNameWs In ws.Names
'Check name is visble.
If objNameWs.Visible = True Then
'Check name refers to a range on the active sheet.
If InStr(1, objNameWs.RefersTo, sWsName, vbTextCompare) Then
sRefersTo = objNameWs.RefersTo
sObjName = objNameWs.Name
'Check name is scoped to the worksheet.
If objNameWs.Parent.Name <> sWbName Then
'Delete the current name scoped to worksheet replacing with workbook scoped name.
sObjName = Mid(sObjName, InStr(1, sObjName, "!") + 1, Len(sObjName))
'Check to see if there already is a Named Range with the same Name with the full workbook scope.
For Each objNameWb In wb.Names
If sObjName = objNameWb.Name Then
MsgBox "There is already a Named range with ""Workbook scope"" named """ + sObjName + """. Change either Named Range names or delete one before running this Macro."
Exit Sub
End If
Next objNameWb
objNameWs.Delete
wb.Names.Add Name:=sObjName, RefersTo:=sRefersTo
End If
End If
End If
Next objNameWs
End Sub
Found this at theexceladdict.com
Select the Named range on your worksheet whose scope you want to change;
Open the Name Manager (Formulas tab) and select the name;
Click Delete and OK;
Click New… and type in the original name back in the Name field;
Make sure Scope is set to Workbook and click Close.
