この記事でわかること
- VBAで名前定義を作成・削除できる（Names.Add / Names.Delete）
- ブック内の全名前定義を一覧取得し、エラーになっている名前を検出・削除できる
- シート上の一覧表から名前定義を一括登録できる
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
—
どんな場面で使う？
名前定義の一括管理が役立つのは、たとえばこんな場面だ。
- ドロップダウンリストのソース範囲を大量に登録したい — 部門名、商品カテゴリ、都道府県など、入力規則で使う名前定義を10個以上まとめて登録する場合。「名前の管理」ダイアログで1つずつ追加するのは時間がかかりすぎる
- テンプレートの名前定義を別のブックに移植したい — 既存のテンプレートで使っている名前定義の構成を、新しいブックにそのまま再現したい場面。一覧表で管理していれば、シートごとコピーしてマクロを実行するだけで済む
- 壊れた名前定義（#REF!エラー）を一括で掃除したい — シートの削除や移動で参照先が壊れた名前定義が溜まっていくと、「名前の管理」ダイアログが汚くなるし、数式の参照先を追うときに紛らわしい
- 定期的に参照先が変わる名前定義を更新したい — 月次集計で「今月の売上範囲」のような名前定義の参照先を毎月更新する場合。一覧表の参照先を書き換えてマクロを実行すれば一括更新できる
- ブック引き継ぎ時に名前定義の一覧を整理して渡したい — 前任者が作ったブックに大量の名前定義があり、どれが使われていてどれが不要か分からない。一覧取得で全体像を把握してから整理できる
名前定義はExcelの便利機能だが、数が増えると手動管理が破綻しやすい。VBAで一括処理すれば、登録も削除も棚卸しも一瞬で終わる。
—
完成イメージ（Before / After）
Before（名前の管理ダイアログで1つずつ手作業）:
| 操作 | 所要時間 |
|---|---|
| 名前定義を1つ追加 | 約30秒 |
| 30個追加 | 約15分 |
| 不要な名前を探して削除 | さらに数分 |
After（VBAで一括処理）:
| 操作 | 所要時間 |
|---|---|
| 一覧表に名前と参照先を記入 | 数分 |
| マクロ実行で一括登録 | 数秒 |
| エラー名前の検出・削除 | 数秒 |
—
自分も以前、数十個の名前定義を1つずつ「名前の管理」ダイアログで追加していた。スクロールして確認して、新規作成して、参照先を入力して……を繰り返すうちに、どこまで設定したか分からなくなることがあった。VBAで一括登録するようにしてからは、一覧表にまとめて書くだけで数十個の名前定義が数秒で完了するようになった。追加漏れもゼロ。同じように名前定義の管理に苦労している人に、この記事で一括処理を体験してほしい。
名前定義の追加・削除は手作業だと数が増えるほど管理が大変になる。VBAで一括処理すれば速くて正確。
なお、名前定義をドロップダウンリスト（入力規則）と組み合わせて使いたい場合は 入力規則（ドロップダウンリスト）をVBAで一括設定 を参照。
—
実行前の準備
バックアップを取る
名前定義を削除すると、その名前を参照している数式が #NAME? エラーになる。 必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロが保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
シート構成を確認する（実務版で使用）
実務版コードは以下のシート構成を前提としている：
- 一覧シート: シート名「名前定義一覧」— A列に名前、B列に参照先を記入
基本版・応用版はシート構成を問わない。
—
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
Alt + F8→ マクロ名を選んで「実行」
ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。
—
コード（基本版）– Names.Addで名前定義を作成 / Names.Deleteで削除
名前定義の作成と削除の基本。まずはこの2つを押さえる。
'============================================================
' ■ 名前定義を作成する（基本版）
' → 指定した名前と参照先で名前定義を追加
'============================================================
Sub CreateNamedRange()
'--- ★書き換えポイント ---
Dim nmName As String
nmName = "売上範囲" '← 名前定義の名前
Dim nmRefersTo As String
nmRefersTo = "=Sheet1!$A$1:$D$100" '← 参照先（= 付き、絶対参照）
'--- ★ここまで ---
'--- 名前定義を追加（同名があれば上書き）
ThisWorkbook.Names.Add Name:=nmName, RefersTo:=nmRefersTo
MsgBox "名前定義「" & nmName & "」を作成しました。", vbInformation
End Sub
'============================================================
' ■ 名前定義を削除する（基本版）
' → 指定した名前の名前定義を削除
'============================================================
Sub DeleteNamedRange()
'--- ★書き換えポイント ---
Dim nmName As String
nmName = "売上範囲" '← 削除する名前定義の名前
'--- ★ここまで ---
'--- 名前定義が存在するか確認してから削除
On Error Resume Next
Dim nm As Name
Set nm = ThisWorkbook.Names(nmName)
On Error GoTo 0
If nm Is Nothing Then
MsgBox "名前定義「" & nmName & "」は存在しません。", vbExclamation
Exit Sub
End If
nm.Delete
MsgBox "名前定義「" & nmName & "」を削除しました。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
nmName |
名前定義の名前 | "売上範囲" |
nmRefersTo |
参照先（作成時のみ） | "=Sheet1!$A$1:$D$100" |
ポイント: RefersTo の値は必ず = 付き・絶対参照（$付き） で指定する。"=Sheet1!$A$1:$D$100" のように書く。相対参照にするとアクティブセルの位置によって参照先がずれる。
基本版コードの処理の解説:
作成コード（CreateNamedRange）では、ThisWorkbook.Names.Add メソッドで名前定義を追加している。Name 引数に名前、RefersTo 引数に参照先を渡すだけのシンプルな構文だ。同名の名前定義が既に存在する場合はエラーにならず上書きされる。これは便利な反面、意図しない上書きに気づきにくいので注意が必要だ。
削除コード（DeleteNamedRange）では、まず On Error Resume Next で一時的にエラーを無視し、指定した名前が存在するか確認している。存在しない名前を直接 Names("名前").Delete で削除しようとすると実行時エラーが発生するため、先に存在チェックを入れている。On Error GoTo 0 でエラー処理を元に戻してから If nm Is Nothing Then で判定するのが定番パターンだ。名前定義の存在確認以外のエラー処理については エラー処理で止まらないマクロを作る方法 で詳しく解説している。
—
コード（応用版）– 全名前定義の一覧取得・エラー名前の削除
ブック内の名前定義が何個あるか分からない、どれがエラーか分からない――そんなときに使う。
'============================================================
' ■ 全名前定義の一覧をイミディエイトウィンドウに出力（応用版）
' → 名前・参照先・スコープを一覧表示
'============================================================
Sub ListAllNames()
Dim nm As Name
Dim cnt As Long
cnt = 0
Debug.Print "===== 名前定義一覧 ====="
Debug.Print "No.", "名前", "参照先", "表示/非表示"
For Each nm In ThisWorkbook.Names
cnt = cnt + 1
Debug.Print cnt, nm.Name, nm.RefersTo, _
IIf(nm.Visible, "表示", "非表示")
Next nm
Debug.Print "===== 合計: " & cnt & " 個 ====="
MsgBox cnt & " 個の名前定義が見つかりました。" & vbCrLf & _
"イミディエイトウィンドウ（Ctrl+G）で一覧を確認してください。", vbInformation
End Sub
'============================================================
' ■ エラーになっている名前定義を検出・削除（応用版）
' → 参照先が壊れている（#REF! など）名前を一括削除
'============================================================
Sub DeleteErrorNames()
Dim nm As Name
Dim rng As Range
Dim errCount As Long
Dim errList As String
errCount = 0
errList = ""
For Each nm In ThisWorkbook.Names
On Error Resume Next
Set rng = nm.RefersToRange
If Err.Number <> 0 Then
errCount = errCount + 1
errList = errList & " " & nm.Name & " → " & nm.RefersTo & vbCrLf
Err.Clear
End If
Set rng = Nothing
On Error GoTo 0
Next nm
'--- エラー名前がなければ終了
If errCount = 0 Then
MsgBox "エラーの名前定義はありませんでした。", vbInformation
Exit Sub
End If
'--- 削除確認
Dim ans As VbMsgBoxResult
ans = MsgBox(errCount & " 個のエラー名前定義が見つかりました。" & vbCrLf & vbCrLf & _
errList & vbCrLf & _
"すべて削除しますか？", vbYesNo + vbExclamation)
If ans <> vbYes Then
MsgBox "削除をキャンセルしました。", vbInformation
Exit Sub
End If
'--- エラー名前を削除
Dim delCount As Long
delCount = 0
For Each nm In ThisWorkbook.Names
On Error Resume Next
Set rng = nm.RefersToRange
If Err.Number <> 0 Then
nm.Delete
delCount = delCount + 1
Err.Clear
End If
Set rng = Nothing
On Error GoTo 0
Next nm
MsgBox delCount & " 個のエラー名前定義を削除しました。", vbInformation
End Sub
応用版コードの処理の解説:
一覧取得コード（ListAllNames）は、For Each nm In ThisWorkbook.Names で全ての名前定義をループし、Debug.Print でイミディエイトウィンドウに出力している。nm.Visible で表示/非表示の区別もチェックしている点がポイントだ。非表示の名前定義は「名前の管理」ダイアログに表示されないため、VBAでしか確認できない。
エラー名前削除コード（DeleteErrorNames）は2段階のループで動いている。1回目のループで RefersToRange プロパティの取得を試み、エラーが発生した名前定義をリストアップする。RefersToRange は参照先をRangeオブジェクトとして返すプロパティで、参照先が壊れている場合（シートが削除された、#REF! になっている等）にはエラーが発生する仕組みだ。リストアップ結果を確認ダイアログで表示し、ユーザーが「はい」を選んだ場合のみ2回目のループで実際に削除を行う。確認ダイアログの使い方については MsgBoxで確認ダイアログを出して処理を分岐する方法 を参照。
名前定義を使ったデータ抽出の効率化については 複数条件でデータを抽出してまとめる方法 も参考になる。
—
コード（実務版）– 一覧表から名前定義を一括登録
実務で名前定義を管理するなら、シート上に一覧表を作って一括登録するのが最も効率的。名前定義の追加・変更・削除がすべて一覧表ベースで管理できる。
一覧表で名前定義を管理するようにしてからは、どの名前がどの範囲を指しているか一目で分かるようになった。引き継ぎ時の説明も楽になった。
'============================================================
' ■ 一覧表から名前定義を一括登録（実務版）
' → 「名前定義一覧」シートのA列=名前、B列=参照先
' → 既存の名前定義はクリアしてから再登録
'============================================================
Sub BulkCreateNames()
'--- ★書き換えポイント ---
Dim listSheet As String
listSheet = "名前定義一覧" '← 一覧表のシート名
Dim nameCol As Long
nameCol = 1 '← 名前が入っている列（A列=1）
Dim refCol As Long
refCol = 2 '← 参照先が入っている列（B列=2）
Dim dataStartRow As Long
dataStartRow = 2 '← データの開始行（1行目はヘッダー）
Dim clearExisting As Boolean
clearExisting = False '← True: 既存の名前定義を全削除してから登録
'--- ★ここまで ---
Dim wsL As Worksheet
Set wsL = ThisWorkbook.Worksheets(listSheet)
'--- 最終行を取得
Dim lastRow As Long
lastRow = wsL.Cells(wsL.Rows.Count, nameCol).End(xlUp).Row
If lastRow < dataStartRow Then
MsgBox "一覧表にデータがありません。", vbExclamation
Exit Sub
End If
'--- 既存の名前定義をクリア（オプション）
If clearExisting Then
Dim ans As VbMsgBoxResult
ans = MsgBox("既存の名前定義をすべて削除してから登録します。" & vbCrLf & _
"よろしいですか？", vbYesNo + vbExclamation)
If ans <> vbYes Then
MsgBox "処理をキャンセルしました。", vbInformation
Exit Sub
End If
Dim nmDel As Name
For Each nmDel In ThisWorkbook.Names
nmDel.Delete
Next nmDel
End If
'--- 一覧表から名前定義を一括登録
Dim addCount As Long
Dim skipCount As Long
Dim errNames As String
addCount = 0
skipCount = 0
errNames = ""
Dim r As Long
For r = dataStartRow To lastRow
Dim nmName As String
nmName = Trim(CStr(wsL.Cells(r, nameCol).Value))
Dim nmRef As String
nmRef = Trim(CStr(wsL.Cells(r, refCol).Value))
'--- 空行はスキップ
If nmName = "" Or nmRef = "" Then
skipCount = skipCount + 1
GoTo NextRow
End If
'--- 参照先の先頭に = がなければ付加
If Left(nmRef, 1) <> "=" Then
nmRef = "=" & nmRef
End If
'--- 名前定義を追加
On Error Resume Next
ThisWorkbook.Names.Add Name:=nmName, RefersTo:=nmRef
If Err.Number <> 0 Then
errNames = errNames & " 行" & r & ": " & nmName & "（" & Err.Description & "）" & vbCrLf
skipCount = skipCount + 1
Err.Clear
Else
addCount = addCount + 1
End If
On Error GoTo 0
NextRow:
Next r
'--- 結果を表示
Dim msg As String
msg = "名前定義の一括登録が完了しました。" & vbCrLf & vbCrLf & _
"登録: " & addCount & " 個" & vbCrLf & _
"スキップ: " & skipCount & " 個"
If errNames <> "" Then
msg = msg & vbCrLf & vbCrLf & "エラー:" & vbCrLf & errNames
End If
MsgBox msg, vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
listSheet |
一覧表のシート名 | "名前定義一覧" |
nameCol |
名前が入っている列 | 1（A列） |
refCol |
参照先が入っている列 | 2（B列） |
dataStartRow |
データの開始行 | 2（1行目はヘッダー） |
clearExisting |
既存の名前定義を全削除するか | False |
一覧表の例
「名前定義一覧」シートに以下のように記入する：
| A列（名前） | B列（参照先） |
|---|---|
| 売上範囲 | Sheet1!$A$1:$D$100 |
| 商品リスト | マスタ!$A$2:$A$50 |
| 税率 | 設定!$B$2 |
| 部門一覧 | マスタ!$C$2:$C$20 |
ポイント: 参照先の先頭に = がなくても自動で付加される。$（絶対参照）は必ず付ける。
実務版コードの処理の解説:
このコードは一覧表シートの各行を上から順にループし、A列の名前とB列の参照先を読み取って Names.Add で登録している。空行はスキップし、参照先の先頭に = がなければ自動で付加する仕組みだ。On Error Resume Next で個別のエラーを捕捉しているため、1行でエラーが出ても残りの行の登録は続行される。エラーが出た行は名前とエラー内容をリストに溜めておき、最後にまとめて表示する。clearExisting = True に設定した場合は、登録前に既存の名前定義を全削除するが、確認ダイアログを挟むので誤操作で全削除されることはない。一覧表ベースで管理する利点は、Excelのシート上で名前と参照先を一覧で確認・編集できること、変更履歴をシートのバージョン管理で残せることだ。
名前定義とドロップダウンリストを組み合わせて使いたい場合は 入力規則（ドロップダウンリスト）をVBAで一括設定 を参照。
—
よくある落とし穴5選
1. 名前定義を削除したら #NAME? エラーが大量発生
自分もこれで焦った。名前定義を一括削除したら、その名前を参照していた数式が全部 #NAME? エラーになった。削除前に参照先を確認する習慣がなかった。
対策: 削除前に Ctrl+F で名前を検索し、数式で使われていないか確認する。応用版の削除コードには確認ダイアログを入れてある。
2. RefersTo に = を付け忘れて参照先がずれる
原因: RefersTo:="Sheet1!$A$1:$D$100" のように = を付け忘れると、文字列としてそのまま登録されてしまう場合がある。
対策: RefersTo:="=Sheet1!$A$1:$D$100" のように必ず先頭に = を付ける。実務版コードでは自動付加する処理を入れてある。
3. 相対参照で名前定義を作ったら参照先がずれた
原因: $ を付けずに "=Sheet1!A1:D100" と指定すると、アクティブセルの位置によって参照先がずれる。
対策: 名前定義の参照先は必ず 絶対参照（$付き） で指定する。"=Sheet1!$A$1:$D$100" のように書く。
4. 同名の名前定義を追加したら上書きされた
原因: Names.Add は同名の名前定義が既にある場合、エラーにならずに上書きする。意図せず参照先が変わってしまう。
対策: 登録前に既存の名前定義を確認するか、実務版コードのように一覧表ベースで管理して上書きを把握する。
5. 非表示の名前定義が残っていて名前が重複した
原因: Visible = False の名前定義は「名前の管理」ダイアログに表示されない。VBAでしか確認できない。
対策: 応用版の一覧取得コードで「表示/非表示」列を出力しているので、非表示の名前定義も把握できる。
6. 名前定義の名前にスペースや特殊文字を使ってエラーになった
原因: 名前定義の名前にはルールがある。先頭は文字またはアンダースコアでなければならず、スペースは使えない。「売上 範囲」のように空白を含むとエラーになる。
対策: スペースの代わりにアンダースコアを使う（例: 売上_範囲）。また、名前の先頭に数字は使えないため、2024年売上 ではなく 売上_2024年 のように書く。セル参照と同じ文字列（A1、R1C1 など）も名前として使えないので注意。
7. For Each ループの中で名前定義を削除するとコレクションがずれる
原因: For Each nm In ThisWorkbook.Names のループ中に nm.Delete を実行すると、Namesコレクションの要素数が変わるため、一部の名前定義がスキップされることがある。
対策: 応用版のエラー名前削除コードのように2回ループを回すか、For i = ThisWorkbook.Names.Count To 1 Step -1 でインデックスを逆順に回す方法が確実だ。行の削除と同じく、下から上（大きい番号から小さい番号）にループするのが鉄則。
VBAで名前定義の作成時にエラーが出るときの対処法
「Names.Addを実行したらエラーになって名前定義が作成できない」という場合、原因は名前の命名ルールに違反していることがほとんどだ。名前定義の名前には「先頭は文字またはアンダースコア」「スペースは使えない」「セル参照と同じ文字列（A1, R1C1等）は不可」「先頭が数字はNG」というルールがある。自分も「2024年売上」という名前を付けようとしてエラーになり、「売上_2024年」に変えたら通った。対処法は、スペースの代わりにアンダースコアを使い、先頭を文字にすること。エラーメッセージだけでは原因が分かりにくいので、名前のルールを事前に把握しておくのが大事だ。
VBAのNames.Addで参照先がずれるときの対処法
「名前定義を作成したのに、参照先が意図したセル範囲と違う」という場合、原因は参照先を相対参照（$なし）で指定していることだ。RefersTo:="=Sheet1!A1:D100" のように $ を付けないと、名前定義を設定した時点のアクティブセルの位置を基準に相対的なオフセットとして解釈されてしまう。対処法は、参照先を必ず絶対参照で書くこと。RefersTo:="=Sheet1!$A$1:$D$100" のように $ を付ければ、どのセルがアクティブでも同じ範囲を指す。実務版コードでは先頭の = を自動付加する処理が入っているが、$ は自分で付ける必要があるので注意してほしい。
—
FAQ
Q1: ブックレベルとシートレベルの名前定義の違いは？
ブックレベルはブック内のどのシートからでも使える。シートレベルは特定のシートでのみ有効。
'--- ブックレベル（既定）
ThisWorkbook.Names.Add Name:="売上範囲", RefersTo:="=Sheet1!$A$1:$D$100"
'--- シートレベル
ThisWorkbook.Worksheets("Sheet1").Names.Add Name:="売上範囲", RefersTo:="=Sheet1!$A$1:$D$100"
Q2: 同じ名前の名前定義を追加したらどうなる？
上書きされる。エラーにはならない。意図しない上書きを防ぐには、登録前に存在確認を入れる：
Dim nm As Name
On Error Resume Next
Set nm = ThisWorkbook.Names("売上範囲")
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "「売上範囲」は既に存在します。", vbExclamation
Exit Sub
End If
Q3: 非表示の名前定義を表示に変えたい
VBAで Visible = True に変更する：
ThisWorkbook.Names("非表示の名前").Visible = True
Q4: 特定の名前定義だけ残して他を全部削除したい
ループ内で条件分岐する：
Dim nm As Name
For Each nm In ThisWorkbook.Names
If nm.Name <> "残したい名前1" And nm.Name <> "残したい名前2" Then
nm.Delete
End If
Next nm
Q5: 名前定義の参照先を変更したい
Names.Add で同じ名前・新しい参照先を指定すると上書きで変更される：
ThisWorkbook.Names.Add Name:="売上範囲", RefersTo:="=Sheet1!$A$1:$F$200"
—
まとめ
- Names.Add: 名前定義の作成。
RefersToは=付き・絶対参照で指定 - Names.Delete: 名前定義の削除。削除前に参照している数式がないか確認
- 一覧取得:
For Each nm In ThisWorkbook.Namesで全名前定義をループ - エラー名前:
RefersToRangeでエラーが出る名前定義を検出・削除 - 一覧表から一括登録: シート上の一覧表で名前定義を管理すれば、追加・変更・引き継ぎが楽
関連記事
- 入力規則（ドロップダウンリスト）をVBAで一括設定 — 名前定義とドロップダウンリストの連携
- 複数条件でデータを抽出してまとめる方法 — 名前定義で範囲管理を効率化
- データの最終行・最終列を正確に取得する方法 — 動的範囲の名前定義に活用
- セルの文字列を一括置換する方法 — 名前定義の参照先を一括変更したい場合
- エラー処理で止まらないマクロを作る方法 — 名前定義の存在チェックでのエラーハンドリング
—
次にやりたくなること
名前定義の一括管理ができるようになったら、次はこのあたりを組み合わせると実務がさらに効率化する。
- 入力規則（ドロップダウンリスト）をVBAで一括設定: 名前定義で作った範囲をドロップダウンリストのソースに指定する定番の組み合わせ
- MsgBoxで確認ダイアログを出して処理を分岐する方法: 名前定義の削除前に確認ダイアログを入れてより安全にする
- データの最終行・最終列を正確に取得する方法: 名前定義の参照先を動的に設定したい場合、最終行を取得して範囲を自動調整できる
- セルの文字列を一括置換する方法: 名前定義の参照先に含まれるシート名を一括で変更したい場面で活用できる
- Excelファイルを自動で開いて処理して閉じる方法: 複数ブックの名前定義を一括で設定・棚卸しする場合、ファイルの自動オープンと組み合わせると効率的
—
もっとカスタマイズしたい場合
「名前定義のルールが複雑（動的範囲やOFFSET関数を使いたい）」「複数ブックの名前定義を一括管理したい」「名前定義の変更履歴を残したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できる。
相談時に伝えると話が早い情報:
- Excel のバージョン / OS
- 名前定義の個数と用途（数式参照・入力規則・マクロなど）
- ブックレベル / シートレベルの使い分けの有無
- 複数ブックでの共有が必要か
コメント