VBAで名前の定義を扱う

VBAで名前の定義を扱う方法に関して以下の項目を見ていきます。
1. 名前の定義とは？
2. 名前の定義を追加する
3. 名前の定義を削除する
4. 名前の定義の一覧を作成する
5. 名前の定義を変更する
6. 名前の定義の存在チェック
7. 名前の定義を使って範囲を指定する

1. 名前の定義とは？

名前とは
セル範囲に付けられている名前のことです。

手動で名前を付けるには
数式バーの左端にある [名前] ボックスをクリックして
名前を直接入力します。

デフォルトの設定では作成した名前は
ブック内であればどのシートからでも参照できます。

数式などで値を参照する際に
セルのアドレスで指定できますが，
名前を付ければ
どのシートのどの範囲という情報は不要になり
名前で範囲を参照することができます。

2. 名前の定義を追加する

名前を追加するにはAddメソッドを使用します。
次のコードはVBAで名前を追加して，
名前を付けた範囲に移動します。

Sub macro20200607a()
'名前の定義を追加する

    Dim sh_name As String
    Dim rng As String
   
    sh_name = ActiveSheet.Name
    rng = ActiveSheet.Range("A1:C10").Address
   
    '名前の定義を追加
    ActiveWorkbook.Names.Add _
        Name:="name1", _
        RefersTo:="=" & sh_name & "!" & rng
   
    '名前の範囲に移動
    Application.Goto Reference:="name1"

End Sub

3. 名前の定義を削除する

名前の定義を削除するにはDeleteメソッドを使います。
次のコードは名前"name1"を削除します。

Sub macro20200607b()

    '名前の定義を削除
    ActiveWorkbook.Names("name1").Delete

End Sub

名前を定義したシートを削除すると
シートは削除されても名前は削除されずに残っています。
そのような名前は参照元がなくなった部分はREFになります。

次のコードは
参照元がなくなりエラーが起こっている名前を
REFが含まれているかで判定して削除します。

Sub macro20200607c()
'参照元がなくなった名前を削除する

    Dim n
   
    For Each n In ActiveWorkbook.Names
        If InStr(n.RefersTo, "REF") <> 0 Then
            n.Delete
        End If
    Next n

End Sub

4. 名前の定義の一覧を作成する

定義された名前はNamesオブジェクトに格納されています。
名前の定義のそれぞれにアクセスするには
Names(1)，Names(2),…のようにします。

次のコードは
Namesオブジェクトを変数nmsに格納してから個々の名前を参照して
名前の定義の一覧を作成します。

Sub macro20200607d()
'名前の定義一覧を作成する

    Dim i As Integer
    Dim nms As Names
   
    Sheets.Add
    Cells(1, 1) = "名前の定義一覧"
    Cells(4, 1) = "名前"
    Cells(4, 2) = "範囲"
   
    Set nms = ActiveWorkbook.Names
    For i = 1 To nms.count
        Cells(i + 4, 1) = nms(i).Name
        Cells(i + 4, 2) = Replace(nms(i).RefersTo, "=", "")
    Next
   
    ActiveSheet.Cells.EntireColumn.AutoFit
   
End Sub

5. 名前の定義を変更する

名前の名前を変更するにはNameプロパティ，
名前の参照を変更するにはRefersToプロパティに
変更したい値を指定します。

次のコードは"name1"という名前の定義を変更します。

Sub macro20200607e()
'名前の定義を変更する
    Dim n As Name
   
    Set n = ActiveWorkbook.Names("name1")
    n.Name = "変更後name1"
    n.RefersTo = "=Sheet5!A3"

End Sub

6. 名前の定義の存在チェック

次のコードは
ブックのすべての名前が格納されているNamesオブジェクトを参照して
定義されている名前と
存在を確認したい名前を比較して
同じであれば名前は存在すると判定します。

Sub macro20200607f()
'名前の定義の存在チェックする

    Dim n_str As String
    Dim n As Name
    Dim nms As Names
   
    n_str = "name4"
   
    Set nms = ActiveWorkbook.Names
    For Each n In nms
        If n.Name = n_str Then
            MsgBox n_str & "は存在します。"
            Exit Sub
        End If
    Next n
   
    MsgBox n_str & "は存在しません。"
   
End Sub

7. 名前の定義を使って範囲を指定する

RefersToRangeプロパティを使うと
名前の定義がされている範囲をRangeオブジェクトとして扱えます。

次のコードは"name1"で定義された範囲に1を入力します。

Sub macro20200607g()
'名前の定義を使って範囲を指定する

    Dim rng As Range
   
    Set rng = ActiveWorkbook.Names("name1").RefersToRange
    rng = 1
   
End Sub

使用Ver：Win10, Excel For Office365