参照先が見つからない名前の定義

シートをコピーすると、「既にある名前 ‘？？？’ が含まれています。この名前を使用しますか？」というメッセージが出ることがあります。

このメッセージは、Excel2002含めそれ以前の古いバージョンのExcelで作成されたブックに名前の定義が設定されている場合にシートのコピーを行うと発生します。そのため、拡張子が.xlsのブックを使っている場合がほとんどだと思われます。

名前の定義が重複することが問題の原因のため、解決方法は名前の定義を削除することです。数式タブ→名前の管理、から「名前の定義」ダイアログを開き、不要な定義を削除すれば基本的にはエラーは出なくなります。

ただ、名前の定義をVBAで非表示にされている場合は「名前の定義」ダイアログには表示されないため問題が解消しません。名前付きセルを使いたがる人に限って非表示にしたりするので迷惑極まりないです。

そこで、自分で名前の定義を確認して手で削除したい場合に非表示になっている名前の定義を表示するマクロと、そういうことはいいので一括で名前の定義を削除したい場合のマクロを紹介します。


非表示になっている名前の定義を表示する

名前の定義はNameオブジェクトで管理されています。NameオブジェクトのVisibleプロパティがFalseに設定されていると非表示になります。

以下のマクロはアクティブブックに含まれる全ての名前の定義のVisibleをTrueにします。

あとは「名前の定義」ダイアログで不要な定義を削除してください。


一括で名前の定義を削除する

以下のマクロはアクティブブックの名前の定義を全て削除します。ただし、印刷範囲を指定する「Print_Area」とタイトル行と列の「Print_Titles」は削除せずに残します。

NameオブジェクトのDeleteメソッドで名前の定義を削除しますが、名前に不正文字が入っているとDeleteメソッドは1004エラーになります。

これはVBAでは回避できず、NameプロパティをVBAのコードで書き換えても正常に処理は通過しますが書き換えは行われません。名前の書き換えは「名前の定義」ダイアログでしか受け付けてもらえない仕様です。

Win32APIを使って「名前の定義」ダイアログを表示させてキーイベントで他の名前に書き換えることも可能ですが、このエラーに遭遇することはそこまでないと思われ、そこまでするとコードも長くなり面倒なのでここでは行いません。

不正文字の名前ののエラーが発生した場合はメッセージを表示し、手で「名前の定義」ダイアログで削除するように促すようにしています。



名前の定義は使わない方がいい？

私はこのエラーの原因になっている「名前付きセル」は一切利用しません。理由は、セルに名前を付けることの恩恵を感じたことは今まで一度もなく、むしろ迷惑を被った記憶しかないためです。

仕事ではどこかのだれかが作ったブックを使うことがありますが、名前の定義の参照先がその当人のPCや聞いたこともないファイルサーバのパスになっていたりします。当然機能しません。そして「名前の定義」ダイアログを表示しない限りこのことに気が付きません。

さらに頭に来るのが本件のエラーメッセージです。「はい」を押せば何度も何度も同じダイアログが表示され、「いいえ」を押せばどうでもいいのに名前の変更を要求されます。×ボタンを押せば全てから解放されるかと思いきや、「いいえ」と同じ動作をします。「すべてはい」ボタンや一発キャンセルの方法がなぜ無いのか本当に疑問です。

本などの説明では「名前を付けることで管理しやすくなる」とか「数式が管理しやすくなる」とか書いてありますが、使うならせめて自分しか使わないブックに限定すべきです。複数人が参照するようなブックだと現場は混乱しかねません。名前付きセルの機能自体がマイナーで多くの人が利用しないのですから。