webdevqa.jp.net

名前付き範囲スコープを変更する方法

Name Managerを使用して名前付き範囲を作成すると、Workbookまたは[worksheet name]スコープを指定するオプションが与えられます。ただし、スコープを変更する場合は、ドロップダウンがグレー表示されます。 Name Managerまたは、できればVBAに、既存の名前付き範囲のスコープを変更する方法はありますか?

例えば:

  • testNameは、スコープWorkbookで'sheet1'!A1:B2を参照します。それをどのように変更しますか
  • testNameは 'sheet1'スコープの'sheet1'!A1:B2を参照しますか?
18
Stevil

自分とJan Karel Pieterseが開発した無料のName Managerアドインを http://www.decisionmodels.com/downloads.htm からダウンロードできます。これにより、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
14

私は解決策を見つけました!名前付き変数を使用してシートをコピーするだけです。次に、元のシートを削除します。コピーされたシートには、同じ名前の変数がありますが、ローカルスコープ(scope =コピーされたシート)があります。

ただし、ローカル変数からグローバル変数に変更する方法がわかりません。

10
Astrid Marie

互いに逆になり、アクティブシート上の範囲を参照するすべての名前付き範囲のスコープ(ワークシートからワークブックへ、または逆へ)を切り替えるこれらの2つのサブを確認します。

 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
10
JS20'07'11

最初から新しい名前を作成し、古い名前を削除します。

2
Dick Kusleika

別の方法は、2007以降のExcelファイルを「ハッキング」することです。ただし、これを行う場合は注意し、元のバックアップを保持することをお勧めします。

最初に、Excelスプレッドシートを.xlsxまたは.xlsmファイル(バイナリではない)として保存します。ファイルの名前を.Zipに変更してから、解凍します。 Zip構造のxlフォルダーに移動し、ワードパッドまたは同様のテキストエディターでworkbook.xmlを開きます。名前付き範囲は、definedNameタグにあります。ローカルスコープはlocalSheetId = "x"によって定義されます(シートIDは、スプレッドシートを開いた状態でExcelでAlt-F11を押すと、VBAウィンドウに移動して、プロジェクトペインを見ることができます)。非表示の範囲はhidden = "1"で定義されるため、たとえば、hidden = "1"を削除して再表示するだけです。

ここで、フォルダー構造の整合性を維持するように注意してフォルダー構造を再圧縮し、名前を.xlsxまたは.xlsmに戻します。

多数の定義済み範囲の範囲を変更したり、非表示/非表示を解除する必要がある場合、これはおそらく最良の解決策ではありませんが、1つまたは2つの小さな調整を行う場合はうまく機能します。

2
Vaskor Basak

すべてのワークシート名をグローバル名に昇格させる方法は次のとおりです。 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
2
codeManJones

私にとっては、Name Managerと同じ範囲に対して新しい名前タグを作成すると、スコープを変更するオプションが提供されます;)ワークブックはデフォルトとして提供され、利用可能なシートのいずれかに変更されました。

1
Yani

これにはさらに改良が必要ですが、既存のローカル名を削除せずに、すべての単純な参照で機能します。

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
1
Peter

JS20'07'11のコードは本当にシンプルで直接的です。私がお伝えしたいのは、条件に感嘆符を付けることです。

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

これにより、誤ったシートにNamedRangeを追加できなくなります。例:NamedRangePlan11という名前のシートを参照しており、Plan1感嘆符を使用しない場合、範囲を追加するときにコードが混乱する可能性があります。

[〜#〜] update [〜#〜]

修正:シートの名前を評価する正規表現を使用するのが最善です。使用できる簡単な関数は次のとおりです( http://blog.malcolmp.com/2010/regular-expressions-Excel-add-in で調整、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

そのため、次のようなものを使用できます。

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

の代わりに

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

これは、Plan1および 'Plan1'(範囲が複数のセルを参照する場合)のバリエーションをカバーします

ヒント:シート名に単一引用符( ')、:)を使用しないでください。

0
Helio Junior

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
0
Wysinawyg

これらの回答は、Workbookスコープで名前付き範囲を定義しようとする際に、同様の問題を解決するのに役立ちました。 「あーは!」私にとっては、Names Collectionを使用することです(これは、whole 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") 

Sheet2がアクティブなときに名前のリストを見ると、両方の範囲がありますが、他のシートに切り替えられ、"a_test_rng1"は存在しません。

これで、適切と思われるスコープで名前付き範囲をコード内で喜んで生成できます。ネームマネージャーやプラグインをいじる必要はありません。


それ以外にも、Excel Mac 2011のネームマネージャーは混乱していますが、名前の範囲のリストを表示しているときに、名前の横にシートが表示されている場合、何を見ているかを伝える列ラベルはありません、その名前の範囲はワークシート/ローカルです。添付のスクリーンショットをご覧ください。

Excel Mac 2011 Name Manager

この記事 への完全な功績は、ピースをまとめることです。

0
Zephyr Mays