Excelには「名前定義」という機能があります。
特定のセルや範囲に名前を付けて、
Range("設定値")
のように参照できる便利な機能です。
セル番地ではなく意味のある名前で参照できるため、うまく使えば数式やVBAの可読性が上がります。
ただし、実務配布用のExcelでは注意が必要です。
名前定義は便利な一方で、
見えない参照
になりやすく、管理しないと事故の元になります。
名前定義のメリット
名前定義は、少数の重要セルや固定範囲に使うと便利です。
例えば、
- 設定値セル
- 入力範囲
- マスタ範囲
- 固定の参照セル
- 数式で意味を持たせたい範囲
- VBAから参照したい固定セル
などです。
セル番地だけで書くより、
Range("開始日")
Range("対象範囲")
Range("設定値")
のように書けるため、意味が分かりやすくなります。
名前定義が事故の元になる場面
一方で、名前定義にはリスクもあります。
特に実務配布用Excelでは、次のような状態が危険です。
- 名前が多すぎる
- 使っていない名前定義が残っている
- 外部ブック参照が残っている
- シートコピーで名前定義が増殖する
- 削除済みシートやセルを参照している
- 他人が名前定義の存在に気づかない
名前定義は、シート上に直接見えません。
そのため、後から保守する人や配布先では、「名前の管理」を開かないと存在に気づきにくいです。
外部参照や参照エラーに注意
名前定義で特に面倒なのが、外部参照や参照エラーです。
別ブックを参照した名前定義が残っていると、ファイルを開いたときに外部リンク警告が出ることがあります。
また、削除済みシートや存在しないセルを参照していると、参照エラーになります。
配布用Excelでこれが出ると、
「このファイルは壊れているのでは？」
と見られやすくなります。
使うなら少数に限定する
名前定義は、使ってはいけない機能ではありません。
ただし、実務では使う範囲を絞った方が安全です。
使うなら、
- 少数の重要セルに限定する
- 用途を説明できる名前だけにする
- 不要な名前定義を残さない
- 外部参照がないか確認する
- 配布前に「名前の管理」を確認する
といった運用が必要です。
名前定義一覧を確認するVBAコード
以下は、ブック内の名前定義を一覧表示するためのサンプルです。
標準モジュールに貼って、テスト用のxlsmで試してください。
A列に名前、B列に参照先を書き出します。
ご注意
以下のVBAコードは、動作イメージを確認するためのサンプルです。
実行前に必ずExcelファイルのバックアップを作成してください。
業務で使用しているファイルに、いきなり貼り付けて実行しないでください。
Excelのバージョンや設定により、動作が異なる場合があります。
A列・B列へ書き込むため、既存データがあるシートでは実行しないでください。
Sub ListDefinedNames()
Dim nm As Name
Dim ws As Worksheet
Dim rowIndex As Long
Set ws = ThisWorkbook.Worksheets(1)
ws.Range("A1").Value = "名前"
ws.Range("B1").Value = "参照先"
rowIndex = 2
For Each nm In ThisWorkbook.Names
ws.Cells(rowIndex, 1).Value = nm.Name
ws.Cells(rowIndex, 2).Value = nm.RefersTo
rowIndex = rowIndex + 1
Next nm
MsgBox "名前定義の一覧を書き出しました。", vbInformation
End Sub
このコードを使うと、ブック内にどのような名前定義が残っているか確認できます。
外部ブック参照や参照エラーが混ざっていないかを確認するきっかけになります。
名前定義を使ってよい場面
名前定義は、次のような場面では有効です。
- 少数の重要な設定セルだけに使う
- セル番地より意味名の方が明らかに分かりやすい
- 参照先が固定されている
- 用途を説明できる
- 名前の管理を定期的に確認できる
例えば、設定値や入力範囲のように、意味を持たせたい固定セルでは便利です。
名前定義を避けた方がよい場面
逆に、次のような場合は避けた方が無難です。
- 大量の名前定義が必要になる
- 名前の用途を説明できない
- 外部ブック参照が混ざる
- シートコピーを頻繁に行う
- 他人に配布するが管理表がない
- 保守担当者が名前定義を把握できない
このような場合は、名前定義よりも、設定シートで見える化した方が安全なことがあります。
設定シートで見える化する選択肢
名前定義は便利ですが、見えにくいという弱点があります。
そのため、実務配布用Excelでは、
- 設定シートを作る
- 項目名と値を表で管理する
- VBAでは設定シートを明示的に参照する
という方法も有効です。
見える場所に設定値を置いておくと、後から保守する人にも説明しやすくなります。
まとめ
Excelの名前定義は、少数の重要セルや固定範囲に使うなら便利です。
ただし、
- 大量に増える
- 使途不明な名前が残る
- 外部参照が混ざる
- 参照エラーが残る
- シートコピーで増殖する
と、配布用Excelでは事故の元になります。
名前定義は便利な機能ですが、見えない参照になりやすい機能でもあります。
実務では、
少数なら便利、増えすぎたら危険
くらいで考えるのがよいと思います。
配布前には、名前の管理を確認し、不要な名前定義や外部参照が残っていないか棚卸しするのが無難です。
詳しい内容はこちら