명명된 범위 범위 변경 방법

명명된 범위 범위 변경 방법

명명된 또는 Name Manager(이름 관리자)를 이 제공됩니다[worksheet name]범위. 하지만 범위를 변경하려면 드롭다운이 회색으로 표시됩니다.Name Manager 또는 VBA에서 기존 명명된 범위의 범위를 변경할 수 있는 방법이 있습니까?

예:

• testName에대한을 'sheet1'!A1:B2Scope Workbook은 "Scope Workbook"입니다.어떻게 변경해야 합니까?
• testName에대한을 'sheet1'!A1:B2' 'sheet1' 로?

해결책을 찾았어요!지정된 변수로 시트를 복사하기만 하면 됩니다.그런 다음 원본 시트를 삭제합니다.이제 복사된 시트에 명명된 변수는 동일하지만 로컬 스코프(scope=복사된 시트)를 사용합니다.

하지만 현지 변수에서 글로벌 변수로 바꾸는 방법을 모르겠습니다.

http://www.decisionmodels.com/downloads.htm 에서 본인과 Jan Karel Pieterse가 개발한 무료 Name Manager addin을 다운로드할 수 있습니다. 이를 통해 Excel 2007 Name Manager가 처리할 수 없는 이름 작업을 많이 수행할 수 있습니다.

VBA의 경우:

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

서로 반대 방향으로 움직이는 이 두 하위 항목을 확인하고 활성 시트의 범위를 나타내는 모든 명명된 범위의 범위(워크시트에서 워크북으로 또는 반대 방향으로)를 뒤집습니다.

 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

다른 방법은 2007년 또는 그 이상의 Excel 파일을 "해킹"하는 것입니다. 하지만 이 작업을 수행하는 경우에는 주의하고 원본의 백업을 보관하는 것이 좋습니다.

먼저 Excel 스프레드시트를 이진 파일이 아닌 .xlsx 또는 .xlsm 파일로 저장합니다.파일 이름을 .zip으로 바꾼 다음 압축을 풉니다.zip 구조의 xl 폴더로 이동하여 wordpad 또는 유사한 텍스트 편집기에서 workbook.xml을 엽니다.명명된 범위는 정의된 이름 태그에 있습니다.로컬 범위 지정은 localSheetId="x"로 정의됩니다(시트 ID는 Excel에서 스프레드시트를 연 상태에서 Alt-F11을 눌러 VBA 창으로 이동한 다음 프로젝트 창을 보면 알 수 있습니다).숨겨진 범위는 hidden="1"로 정의되므로, 예를 들어 숨김을 해제하려면 hidden="1"을 삭제하기만 하면 됩니다.

이제 폴더 구조를 다시 압축하여 폴더 구조의 무결성을 유지하고 이름을 다시 .xlsx 또는 .xlsm으로 변경합니다.

정의된 범위를 많이 변경하거나 숨김/숨김 해제해야 하는 경우에는 이 방법이 가장 적합하지 않을 수 있습니다. 단, 한두 개의 작은 조정을 수행하는 데는 문제가 없습니다.

새 이름을 처음부터 만들고 이전 이름을 삭제합니다.

이 답변들은 이름이 지정된 범위를 정의하는 동안 유사한 문제를 해결하는 데 도움이 되었습니다.Workbook범위. 저에게 "아-HA!"는 전체 워크북에 상대적인 이름 모음을 사용하는 것입니다!이것은 많은 사람들에게 명백한 사실을 다시 말해주는 것일 수도 있지만, 제 연구에서 명확하게 언급되지 않았기 때문에 저는 비슷한 질문을 가진 다른 사람들에게 공유합니다.

' 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") 

되었을 때 의 이름 두 모두 Sheet으로 하고 Sheet2는 Sheet2와 Sheet의 이름 을 보면,"a_test_rng1"없습니다.

이제 내가 적절하다고 생각하는 범위에 상관없이 코드에 명명된 범위를 생성할 수 있습니다.이름 관리자나 플러그인을 함부로 사용할 필요가 없습니다.

---

또한 Excel Mac 2011의 이름 관리자는 엉망이지만 명명된 범위 목록을 보는 동안 무엇을 보고 있는지 알려주는 열 레이블이 없지만 이름 옆에 시트가 나열되어 있으면 해당 이름이 워크시트/로컬로 범위가 지정됩니다.첨부된 스크린샷을 참조하십시오.

그 조각들을 조립한 이 기사에 전적으로 공을 돌립니다.

모든 워크시트 이름을 글로벌 이름으로 승격하는 방법은 다음과 같습니다.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

그러나 기존 로컬 이름을 삭제하지 않고 모든 단순 참조에서 작동합니다.

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

이름 관리자에서 동일한 범위의 새 이름 태그를 만들면 범위를 변경할 수 있는 옵션이 제공됩니다.) 워크북이 기본으로 제공되며 사용 가능한 모든 시트로 변경할 수 있습니다.

JS20'07'11의 코드는 정말 놀랍도록 간단하고 직접적입니다.제가 제안하고 싶은 한 가지는 다음과 같은 조건에 느낌표를 붙이는 것입니다.

InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare)

이렇게 하면 잘못된 시트에 명명된 범위를 추가할 수 없기 때문입니다.예: NamedRange가 Plan11이라는 시트를 참조하고 Plan1이라는 시트가 있는 경우 느낌표를 사용하지 않으면 범위를 추가할 때 코드가 약간 혼란스러울 수 있습니다.

갱신하다

수정:시트의 이름을 평가하려면 정규식을 사용하는 것이 가장 좋습니다.사용할 수 있는 간단한 기능은 다음과 같습니다(http://blog.malcolmp.com/2010/regular-expressions-excel-add-in, 에서 Microsoft VBScript 정규 표현식 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

다음과 같은 기능을 사용할 수 있습니다.

xMatch("'?" +sWsName + "'?" + "!", objName.RefersTo, 1) <> ""

대신에

InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare)

여기에는 Plan1 및 'Plan1'(범위가 둘 이상의 셀을 참조하는 경우) 변형이 포함됩니다.

팁: 작은 따옴표('), :)가 있는 시트 이름은 피합니다.

JS20'07'11의 이전 Makro에 몇 줄의 코드를 추가하여 시트의 Named Ranges 이름이 이미 워크북의 Named Ranges 이름이 아님을 확인했습니다.이러한 줄이 없으면 이미 정의된 워크북의 범위 이름이 지정된 범위가 삭제되고 대체됩니다.

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

theexceladdict.com 에서 찾을 수 있습니다.

• 워크시트에서 범위를 변경할 명명된 범위를 선택합니다.

• 이름 관리자(공식 탭)를 열고 이름을 선택합니다.

• 삭제 및 확인을 클릭합니다.

• 새로 만들기…를 클릭하고 이름 필드에 원래 이름을 다시 입력합니다.

• Scope가 Workbook으로 설정되어 있는지 확인하고 Close(닫기)를 클릭합니다.

언급URL : https://stackoverflow.com/questions/1057189/how-to-change-named-range-scope