vba 如何更改命名范围范围

How to change Named Range Scope

提问by Stevil

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?

当我通过名称管理器创建命名范围时，我可以选择指定工作簿或[worksheet name]范围。但是如果想改变范围，下拉菜单是灰色的。有没有办法在名称管理器或 VBA 中更改现有命名范围的范围？

For example:

例如：

• testNamerefers to 'sheet1'!A1:B2with scope Workbook. How would I change that to
• testNamerefers to 'sheet1'!A1:B2with 'sheet1' scope?

• testName指'sheet1'!A1:B2具有范围的工作簿。我将如何将其更改为
• testName指的是'sheet1'!A1:B2“sheet1”范围？

采纳答案by Charles Williams

You can download the free Name Manager addin developed by myself and Jan Karel Pieterse from http://www.decisionmodels.com/downloads.htmThis enables many name operations that the Excel 2007 Name manager cannot handle, including changing scope of names.

您可以从http://www.decisionmodels.com/downloads.htm下载由我自己和 Jan Karel Pieterse 开发的免费名称管理器插件 。这可以实现许多 Excel 2007 名称管理器无法处理的名称操作，包括更改名称范围。

In VBA:

在 VBA 中：

Sub TestName()
Application.Calculation = xlManual
Names("TestName").Delete
Range("Sheet1!$A:$B").Name = "Sheet1!TestName"
Application.Calculation = xlAutomatic
End Sub

回答by Astrid Marie

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..

但是，我不知道如何从局部变量更改为全局变量..

回答by JS20'07'11

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

回答by Vaskor Basak

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:

另一种方法是“破解” 2007 或更高版本的 Excel 文件，但建议您在这样做时小心谨慎，并保留原始文件的备份：

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.

首先将 Excel 电子表格另存为 .xlsx 或 .xlsm 文件（不是二进制文件）。将文件重命名为 .zip，然后解压缩。转到 zip 结构中的 xl 文件夹，然后在写字板或类似的文本编辑器中打开 workbook.xml。在 definedName 标签中可以找到命名范围。本地范围由 localSheetId="x" 定义（可以通过在 Excel 中按 Alt-F11 找到工作表 ID，打开电子表格，转到 VBA 窗口，然后查看“项目”窗格）。例如，隐藏范围由 hidden="1" 定义，因此只需删除 hidden="1" 即可取消隐藏。

Now rezip the folder structure, taking care to maintain the integrity of the folder structure, and rename back to .xlsx or .xlsm.

现在重新压缩文件夹结构，注意保持文件夹结构的完整性，然后重命名回 .xlsx 或 .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.

如果您需要更改范围或隐藏/取消隐藏大量定义的范围，这可能不是最佳解决方案，尽管它可以很好地进行一两个小调整。

回答by Dick Kusleika

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

从头开始创建新名称并删除旧名称。

回答by codeManJones

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

回答by Peter

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

回答by Yani

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

对我来说，当我从名称管理器为相同范围创建新的名称标签时，它可以让我选择更改范围；) 工作簿是默认的，可以更改为任何可用的工作表。

回答by Zephyr Mays

These answers were helpful in solving a similar issue while trying to define a named range with Workbookscope. The "ah-HA!" for me is to use the Names Collectionwhich 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.

这些答案有助于在尝试定义具有Workbook范围的命名范围时解决类似问题。“啊哈！” 对我来说是使用与整个工作簿相关的名称集合！这可能对许多人来说是显而易见的，但在我的研究中并没有明确说明，所以我与其他人分享了类似的问题。

' 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.

如果您在 Sheet2 处于活动状态时查看名称列表，则两个范围都在那里，但切换到任何其他工作表，并且"a_test_rng1"不存在。

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.

另外，Excel Mac 2011 中的名称管理器是一团糟，但我确实发现，虽然没有列标签告诉您在查看命名范围列表时正在查看的内容，但如果名称旁边列出了一个工作表，该名称的范围为 worksheet / local。见附截图。

Full credit to this articlefor putting together the pieces.

完全归功于这篇文章将这些作品放在一起。

回答by Wysinawyg

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.

我在 JS20'07'11 之前的 Makro 中添加了一些额外的代码行，以确保工作表的命名范围的名称不是工作簿的命名范围的名称。如果没有这些行，已定义的工作簿获取命名范围将被删除和替换。

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