[vba] How to change Named Range Scope

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 to
• testName refers to 'sheet1'!A1:B2 with 'sheet1' scope?

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

I found the solution! Just copy the sheet with your named variables. Then delete the original sheet. The copied sheet will now have the same named variables, but with a local scope (scope= the copied sheet).

However, I don't know how to change from local variables to global..

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 ('), :) .

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

For me it works that when I create new Name tag for the same range from the Name Manager it gives me the option to change scope ;) workbook comes as default and can be changed to any of the available sheets.

create the new name from scratch and delete the old one.

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

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.

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.

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