－ちょっと使えるネタを紹介－
Excel VBAで、名前の定義を取得する方法について、ご紹介します。名前の定義で取得できるデータは、「名前」、「参照先」、「親要素」、「シート名」があって、「Name」、「RefersTo」、「Parent.Name」で取得できます。VBAで名前の定義を取得して、作業を効率化していきましょう。
この記事では、名前の定義を取得する方法について、ご紹介します。
名前の定義で、取得するデータは、「名前」や「参照先」、「親要素」、「シート名」があります。
「名前」や「参照先」、「親要素」、「シート名」は、「Name」、「RefersTo」、「Parent.Name」で取得することができます。
VBAを使うと、選択したセル範囲で、定義されている名前を取得したりするなど、応用が利きます。
VBAで名前の定義を取得して、業務を効率化していきましょう。
では、名前の定義を取得する方法について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
VBAで「名前の定義」を取得する方法について、ご紹介します。
名前の定義で、「名前」と「参照先」、「親要素」、「シート名」を取得してみます。
こんな感じで取得できます。
という感じです。
名前の定義は、「ブック単位」と「シート単位」があります。
「ブック単位」と「シート単位」で、結果が異なります。
それぞれで、名前の定義の「名前」と「参照先」、「親要素」、「シート名」を取得してみます。
ブック単位で定義した名前で、「名前」と「参照先」、「親要素」、「シート名」を取得してみます。
VBAコードは、こんな感じです。
Sub TEST1()
Dim a
Set a = ActiveSheet.Cells(1, 1).Name '名前の定義を取得
Debug.Print "名前：" & a.Name '名前を取得
Debug.Print "参照先：" & a.RefersTo '参照先を取得
Debug.Print "親要素：" & a.Parent.Name '親要素を取得
Debug.Print "シート名：" & Range(a).Parent.Name 'シート名を取得
End Sub
セルA1に、ブック単位で「名前1」という名前を定義しました。
では、VBAコードを実行して、名前の定義を取得してみます。
ブック単位の名前の定義を、取得できました。
ブック単位なので、「親要素」が「TEST.xlsm」でブック名となっています。
次は、シート単位で定義した名前で、「名前」と「参照先」、「親要素」、「シート名」を取得してみます。
VBAコードは、先ほどと同じです。
Sub TEST2()
Dim a
Set a = ActiveSheet.Cells(1, 1).Name '名前の定義を取得
Debug.Print "名前：" & a.Name '名前を取得
Debug.Print "参照先：" & a.RefersTo '参照先を取得
Debug.Print "親要素：" & a.Parent.Name '親要素を取得
Debug.Print "シート名：" & Range(a).Parent.Name 'シート名を取得
End Sub
セルA1に、シート単位で「名前1」という名前を定義しました。
では、VBAコードを実行して、名前の定義を取得してみます。
シート単位の名前の定義を、取得できました。
シート単位なので、「親要素」が「Sheet1」となっています。
名前も、「シート名」が「名前」の最初について、「Sheet1!名前1」となっています。
こんな感じで、名前、参照先、親要素、シート名を、「Name」、「RefersTo」、「Parent.Name」で取得することができます。
VBAで、全ての名前の定義を、取得してみます。
ブックに定義されている名前を、管理することができるので便利です。
名前の定義には、「ブック単位」、「シート単位」があるので、次のパターンで名前の定義を取得してみます。
では、それぞれのパターンで、名前の定義を取得してみます。
ブック内の全ての名前の定義を、取得してみます。
VBAコードは、次のようになります。
Sub TEST3()
i = 1
'ブック内の名前の定義で、ループ
For Each a In ActiveWorkbook.Names
i = i + 1
ActiveSheet.Cells(i, 1) = a.Name '名前を取得
ActiveSheet.Cells(i, 2) = "'" & a.RefersTo '参照先を取得
ActiveSheet.Cells(i, 3) = a.Parent.Name '親要素を取得
ActiveSheet.Cells(i, 4) = Range(a).Parent.Name 'シート名
Next
End Sub
「For Each」を使って、「ActiveWorkbook.Names」内の名前を取得します。
次のような名前の定義を、設定しました。
Sheet1とSheet2に、ブック単位とシート単位で、複数の名前の定義を設定しています。
では、全ての名前の定義を取得してみます。
ブック内の全ての名前の定義を取得できました。
次は、「ブック単位」の名前の定義を、取得してみます。
VBAコードは、次のようになります。
Sub TEST4()
i = 1
'ブック内の名前の定義で、ループ
For Each a In ActiveWorkbook.Names
'親要素が、ブック名の場合
If a.Parent.Name = ThisWorkbook.Name Then
i = i + 1
ActiveSheet.Cells(i, 1) = a.Name '名前を取得
ActiveSheet.Cells(i, 2) = "'" & a.RefersTo '参照先を取得
ActiveSheet.Cells(i, 3) = a.Parent.Name '親要素を取得
ActiveSheet.Cells(i, 4) = Range(a).Parent.Name 'シート名を取得
End If
Next
End Sub
ブック内の名前の定義を取得して、「親要素」が「ブック名と同じ」場合に、名前を取得します。
次のような名前の定義を、設定しました。
Sheet1とSheet2に、ブック単位とシート単位で、複数の名前の定義を設定しています。
では、ブック単位の名前の定義を取得してみます。
ブック単位の名前の定義を取得できました。
次は、「シート単位」の名前の定義を、取得してみます。
「Sheet2」の名前の定義を取得するVBAコードは、次のようになります。
Sub TEST5()
i = 1
'シートの名前の定義で、ループ
For Each a In Worksheets("Sheet2").Names
i = i + 1
ActiveSheet.Cells(i, 1) = a.Name '名前を取得
ActiveSheet.Cells(i, 2) = "'" & a.RefersTo '参照先を取得
ActiveSheet.Cells(i, 3) = a.Parent.Name '親要素を取得
ActiveSheet.Cells(i, 4) = Range(a).Parent.Name 'シート名を取得
Next
End Sub
ポイントは、「Worksheets("Sheet2").Names」を使って、Sheet2の名前の定義だけをループするところです。
次のような名前の定義を、設定しました。
Sheet1とSheet2に、ブック単位とシート単位で、複数の名前の定義を設定しています。
では、シート単位の名前の定義を取得してみます。
シート単位の名前の定義を取得できました。
次は、「特定シートに定義されている」名前の定義を、取得してみます。
シート単位の名前の定義ではなく、特定シートに定義されている、名前の定義を全て取得します。
こっちの方が実務的ですよね。
「Sheet2」に定義されている名前の定義を取得するVBAコードは、次のようになります。
Sub TEST6()
i = 1
'シートの名前の定義で、ループ
For Each a In ActiveWorkbook.Names
If Range(a).Parent.Name = "Sheet2" Then
i = i + 1
ActiveSheet.Cells(i, 1) = a.Name '名前を取得
ActiveSheet.Cells(i, 2) = "'" & a.RefersTo '参照先を取得
ActiveSheet.Cells(i, 3) = a.Parent.Name '親要素を取得
ActiveSheet.Cells(i, 4) = Range(a).Parent.Name 'シート名を取得
End If
Next
End Sub
ポイントは、「Range(a).Parent.Name」を使って、「シート名」が「Sheet2」である場合だけ、名前の定義を取得します。
次のような名前の定義を、設定しました。
Sheet1とSheet2に、ブック単位とシート単位で、複数の名前の定義を設定しています。
では、Sheet2内に定義された名前の定義を取得してみます。
Sheet2内に定義された名前の定義を取得できました。
こっちの方が使うかと思います。
こんな感じで、「For Each」を使えば、ブック内やブック単位、シート単位、特定シートに定義された、名前の定義を取得することができます。
実務で使う場合を考えてみました。
選択した範囲に含まれている、名前の定義を取得する、というのをやってみます。
選択した範囲内の、名前の定義を取得するVBAコードは、こんな感じになります。
Sub TEST7()
'ブック内の名前の定義で、ループ
For Each a In ActiveWorkbook.Names
'選択したセル範囲と、名前の定義の範囲が、一致した場合
If Not Intersect(Range(a.RefersTo), Selection) Is Nothing Then
Debug.Print a.Name '名前を取得
End If
Next
End Sub
ポイントは、「Intersect」を使って、名前の定義のセル範囲が、選択した範囲に含まれているかを、判定するところです。
あらかじめ、「名前1」～「名前8」の値を定義しておきました。
では、取得したいセル範囲を、選択します。
VBAコードを実行して、選択した範囲の名前の定義を取得します。
選択した範囲の「名前」を取得できました。
作業しているシートで、設定されている名前の定義を確認したい、といった場合に使えます。
この記事では、名前の定義を取得する方法について、ご紹介しました。
名前の定義で、取得するデータは、「名前」や「参照先」、「親要素」、「シート名」がありました。
「名前」や「参照先」、「親要素」、「シート名」は、「Name」、「RefersTo」、「Parent.Name」で取得することができます。
VBAを使うと、選択したセル範囲で、定義されている名前を取得したりするなど、応用が利きます。
VBAで名前の定義を取得して、業務を効率化していきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する