セル範囲の名前の設定と削除｜ Excel作業をVBAで効率化

Excelでのセル範囲の名前の設定と削除

セルはA1形式やR1C1形式での座標で表されますが、それとは別に、セル範囲に「名前」を付けて座標の変わりに使うことができます。

Excelでの名前を付ける手順は、名前を付けたいセル範囲を選択して、数式バーの左にある「名前ボックス」に任意の名前を入力します。

名前を削除したい場合は、数式タブ→定義された名前→名前の管理をクリックすると、名前の管理ダイアログが表示されるので、対象の名前を選択→削除ボタン、となります。

以降ではVBAで同じ操作を行う方法を紹介します。

名前の設定

RangeオブジェクトのNameプロパティは、Rangeオブジェクトに指定したセル範囲の名前の取得や設定を行うことができます。

ここで付けた名前を利用することで、セル範囲を参照する際にA1などのセルを指定せずに名前で指定することが可能になります。

名前の設定にはNameプロパティをそのまま使う方法と、NamesコレクションにAddメソッドで追加する方法の２通りがあります。どちらでも構いません。

名前の設定と参照の構文

参照時

Rangeオブジェクト.Name

1	Rangeオブジェクト.Name

設定時

Rangeオブジェクト.Name = 設定する名前
Workbookオブジェクト.Names.Add Name:="名前", RefersTo:=Rangeオブジェクト

1 2	Rangeオブジェクト.Name = 設定する名前 Workbookオブジェクト.Names.Add Name:="名前", RefersTo:=Rangeオブジェクト

Nameプロパティのサンプルコード

Sub NameTest()
    '// A1が含まれる表範囲に名前を設定
    '// Nameプロパティで設定
    Range("A1").CurrentRegion.Name = "一覧表"
    '// Addメソッドで設定
'    Call ActiveWorkbook.Names.Add(Name:="一覧表", RefersTo:=Range("A1").CurrentRegion)
    
    '// 設定した名前のセル背景色を設定
    Range("一覧表").Interior.Color = RGB(90, 255, 110)
    '// 設定した名前のセル範囲の先頭行に背景色を設定
    Range("一覧表").Rows(1).Interior.Color = RGB(200, 200, 200)
    '// 設定した名前のセル範囲を選択
    Range("一覧表").Select
End Sub

Sub NameTest()

'// A1が含まれる表範囲に名前を設定

'// Nameプロパティで設定

Range("A1").CurrentRegion.Name = "一覧表"

'// Addメソッドで設定

' Call ActiveWorkbook.Names.Add(Name:="一覧表", RefersTo:=Range("A1").CurrentRegion)

'// 設定した名前のセル背景色を設定

Range("一覧表").Interior.Color = RGB(90, 255, 110)

'// 設定した名前のセル範囲の先頭行に背景色を設定

Range("一覧表").Rows(1).Interior.Color = RGB(200, 200, 200)

'// 設定した名前のセル範囲を選択

Range("一覧表").Select

End Sub

４行目と６行目は同じ結果になります。書き方がNameプロパティでの設定か、Namesコレクションに対してAddメソッドで追加する設定かの違いです。どちらで書いても構いません。

マクロ実行前

マクロ実行後

NameプロパティとNamesコレクションの違い

Nameプロパティで名前を付けると、その名前はブックのNamesコレクションに追加されます。そのため、違うシートの名前もNamesコレクションにまとめられます。Excelではこのように表示されます。

画像の「商品小計」などの名前のそれぞれが別シートのNameプロパティで作成されたNameオブジェクトの１つで、それらをまとめて表示している名前の管理ダイアログがNamesコレクションになります。

このNameプロパティとNamesコレクションの違いはコードを書く際に認識しておく必要があります。

Namesコレクションの名前の参照

Nameプロパティで名前の設定を行うと、Namesコレクションに追加されますが、その追加されるものはNameオブジェクトになります。ややこしいですね。

Namesコレクションには名前の管理ダイアログで表示される順でNameオブジェクトが格納されています。追加順ではありません。

Namesコレクションから名前を参照する構文

Workbookオブジェクト.Names(インデックス)

1	Workbookオブジェクト.Names(インデックス)

Namesコレクションから名前を取得するサンプルコード

最初に設定した名前を取得する場合は以下のように「Names(インデックス).Name」と書きます。

Sub GetNameTest()
    Range("B1").Name = "B1の名前"
    Range("B2").Name = "B2の名前"
    Range("B3").Name = "B3の名前"
    Range("B4").Name = "B4の名前"
        
    Debug.Print Names(3).Name
End Sub

Sub GetNameTest()

Range("B1").Name = "B1の名前"

Range("B2").Name = "B2の名前"

Range("B3").Name = "B3の名前"

Range("B4").Name = "B4の名前"

Debug.Print Names(3).Name

End Sub

実行結果
B3の名前

名前の編集

一度付けた名前はあとで変更することができます。

再度Nameプロパティを付け直せば変更されます。その際にセル範囲も変更することができます。

以下のコードは、最初にB5セルに名前を付けて、そのあとにセル範囲を変更し、最後に名前を変更します。

Sub ChangeNameTest()
    Range("B5").Name = "B5の名前"
    
    ActiveWorkbook.Names("B5の名前").RefersTo = Range("A5:B5")
    ActiveWorkbook.Names("B5の名前").Name = "最終行の名前"
End Sub

Sub ChangeNameTest()

Range("B5").Name = "B5の名前"

ActiveWorkbook.Names("B5の名前").RefersTo = Range("A5:B5")

ActiveWorkbook.Names("B5の名前").Name = "最終行の名前"

End Sub

名前の削除

名前の削除はDeleteメソッドを使用します。

以下のコードはいったん作成した名前をあとで削除しています。

Sub DeleteNameTest()
    Range("B6").Name = "B6の名前"
    
    ActiveWorkbook.Names("B6の名前").Delete
End Sub

Sub DeleteNameTest()

Range("B6").Name = "B6の名前"

ActiveWorkbook.Names("B6の名前").Delete

End Sub

ブックの全ての名前を削除

人からもらったブックなどに多いのですが、全然使われていない名前が残っていて邪魔だったりすることがあります。

そのような場合に一括でブックの名前を全て削除するには以下のように書きます。

Namesコレクションを全部ループして、各Nameオブジェクトを削除します。

Sub AllDeleteNameTest()
    Dim n As Name
    Dim allName As Names
    
    Set allName = ActiveWorkbook.Names
    
    For Each n In allName
        n.Delete
    Next
End Sub

Sub AllDeleteNameTest()

Dim n As Name

Dim allName As Names

Set allName = ActiveWorkbook.Names

For Each n In allName

n.Delete

End Sub

Excelでのセル範囲の名前の設定と削除

名前の設定

名前の設定と参照の構文

Nameプロパティのサンプルコード

NameプロパティとNamesコレクションの違い

Namesコレクションの名前の参照

Namesコレクションから名前を参照する構文

Namesコレクションから名前を取得するサンプルコード

名前の編集

名前の削除

ブックの全ての名前を削除

関連記事