大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、名前の定義を取得する方法について、ご紹介します。名前の定義で取得できるデータは、「名前」、「参照先」、「親要素」、「シート名」があって、「Name」、「RefersTo」、「Parent.Name」で取得できます。VBAで名前の定義を取得して、作業を効率化していきましょう。

はじめに

この記事では、名前の定義を取得する方法について、ご紹介します。

名前の定義で、取得するデータは、「名前」や「参照先」、「親要素」、「シート名」があります。

「名前」や「参照先」、「親要素」、「シート名」は、「Name」、「RefersTo」、「Parent.Name」で取得することができます。

VBAを使うと、選択したセル範囲で、定義されている名前を取得したりするなど、応用が利きます。

VBAで名前の定義を取得して、業務を効率化していきましょう。

では、名前の定義を取得する方法について、解説していきます。

この記事で紹介すること

  • 名前の定義を取得する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAで名前の定義を取得する

VBAで「名前の定義」を取得する方法について、ご紹介します。

名前の定義で、「名前」と「参照先」、「親要素」、「シート名」を取得してみます。

こんな感じで取得できます。

  • 名前:セル.Name.Name
  • 参照先:セル.Name.RefersTo
  • 親要素:セル.Name.Parent.Name
  • シート名:Range(セル.Name).Parent.Name

という感じです。

名前の定義は、「ブック単位」と「シート単位」があります。

「ブック単位」と「シート単位」で、結果が異なります。

それぞれで、名前の定義の「名前」と「参照先」、「親要素」、「シート名」を取得してみます。

ブック単位の名前の定義を取得

ブック単位で定義した名前で、「名前」と「参照先」、「親要素」、「シート名」を取得してみます。

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」という名前を定義しました。

「ブック単位」の名前の定義を設定しておいた

セルA1にブック単位で名前の定義をしておく
名前の定義を名前の管理で確認

では、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」という名前を定義しました。

「シート単位」の名前の定義を設定しておいた

セルA1にシート単位で名前の定義をしておく
名前の定義を名前の管理で確認

では、VBAコードを実行して、名前の定義を取得してみます。

「シート単位」の名前の定義を取得

シート単位の名前の定義を取得した結果

シート単位の名前の定義を、取得できました。

シート単位なので、「親要素」が「Sheet1」となっています。

名前も、「シート名」が「名前」の最初について、「Sheet1!名前1」となっています。

こんな感じで、名前、参照先、親要素、シート名を、「Name」、「RefersTo」、「Parent.Name」で取得することができます。

VBAで全ての名前の定義を取得

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」の「シート単位」の名前の定義を取得

シート単位の名前の定義を取得した結果

シート単位の名前の定義を取得できました。

特定シートの名前の定義を取得

次は、「特定シートに定義されている」名前の定義を、取得してみます。

シート単位の名前の定義ではなく、特定シートに定義されている、名前の定義を全て取得します。

こっちの方が実務的ですよね。

「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内に定義された」名前の定義を取得

特定シートの名前の定義を取得した結果

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」の値を定義しておきました。

名前の定義を設定しておいた

名前1の名前を定義
名前8の名前を定義
名前の管理の画面

では、取得したいセル範囲を、選択します。

取得したいセル範囲を選択

取得したいセル範囲を選択

VBAコードを実行して、選択した範囲の名前の定義を取得します。

「選択したセル範囲」の名前の定義を取得

選択した範囲の名前の定義を取得した結果

選択した範囲の「名前」を取得できました。

作業しているシートで、設定されている名前の定義を確認したい、といった場合に使えます。

おわりに

この記事では、名前の定義を取得する方法について、ご紹介しました。

名前の定義で、取得するデータは、「名前」や「参照先」、「親要素」、「シート名」がありました。

「名前」や「参照先」、「親要素」、「シート名」は、「Name」、「RefersTo」、「Parent.Name」で取得することができます。

VBAを使うと、選択したセル範囲で、定義されている名前を取得したりするなど、応用が利きます。

VBAで名前の定義を取得して、業務を効率化していきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す