適材適所

WindowsやPowerShellやネットワーク、IBMなどのシステム系の話など気になったことも載せているブログです。

【Excel VBA】名前の定義を削除する【幽霊定義】

Excelの機能の1つに名前の定義というものがあります。

セル範囲に名前を付けることで、その名前でそのセル範囲を呼び出せるというものです。

うまく使えば便利なんですが、人からもらったブックなど歴史があるのものだったりすると、予想外のところに名前が定義されていることがあります。

シートをコピーしようとすると・・・

f:id:shinmai_papa:20210320173513p:plain

一見、「何これ!?」って感じですよね。

いきなりこんなメッセージボックスが出てきても意味不明ですよね。

シートをコピーするときに名前の定義も一緒にコピーしようとするために出てくるメッセージです。

大抵名前が定義されていることすら知らないような場合は形骸化している定義の可能性が高いです。

(調査は必要ですが・・・)

不要ということであれば、今後の憂いを除くために削除してあげた方が良いですね。

f:id:shinmai_papa:20210320173517p:plain

数式⇒名前の管理で削除することができます。

普通はこれで削除することができます。

これでオッケーと思って意気揚々とシートをコピーしようとすると、まだ同じようなメッセージが出ることがあります。

一覧にないのにある・・・まるで幽霊のような名前の定義が存在することがあるのです。

それらはGUIでは見ることができないので、VBAで削除する必要があります。

VBAで名前の定義を削除する

単純に名前の定義を削除するだけなら、とーーっても簡単。

 
Sub nameDeleteSample()
   Dim tmpName As Name
   For Each tmpName In Names
      tmpName.Delete
   Next tmpName
End Sub

単純にNamesオブジェクト内のNameオブジェクトを全て削除しています。

条件付けをする場合はifでNameオブジェクトのNameプロパティを参照すればいいのですが、

文で書くと意味不明ですね。

コードはこんな感じです。

 
Sub nameDeleteSample2()
   Dim tmpName As Name
   For Each tmpName In Names
      'NameオブジェクトのNameプロパティを参照する
         If tmpName.Name Like "*test*" Then
               tmpName.Delete
         End If
   Next tmpName
End Sub

慣れないと混乱するところですが、tmpNameはNameオブジェクトです。

その名前を参照するためにはtmpNameのNameプロパティを呼び出します。

うーん、難解。

幽霊定義

さて、本題。

どこにも名前の定義がないのにシートをコピーしようとしたら出てくるメッセージボックス。

この、見えない名前の定義。

こいつは何なのか。

どうやら、

1.古いバージョンのExcelを新しいバージョンで開いたとき

2.フィルタを使っているとき

3.NameオブジェクトのVisibleプロパイがfalseになっているとき

この辺りが悪さをするようです。

1と2は謎です。

もはやExcelのバグなんでしょうか。

私も実際に遭遇したことがあります。

GUIではどうやっても消せなかったので上述のコードで消しました。

いやらしいのは3番の、NameオブジェクトのVisibleプロパティがfalseになっているとき。

これは誰かが意図的にやらなくては発生しません。

何かよっぽどの理由があって非表示にしてたのだと思うのですが、後でメンテナンスする側のことを考えると、やめてほしい限りでです。

ちなみに名前の定義を非表示にするコードは下記の通りです。

 
Sub nameVisibleSample()
   Dim tmpName As Name
   For Each tmpName In Names
      tmpName.Visible = True
   Next tmpName
End Sub

Visibleプロパティを指定することで可視不可視を切り替えることができます。

本当に罠です・・・

こういう罠はできるだけなくすように運用したいものです。

Namesオブジェクトの補足

さも当たり前のようにFor each の対象として、Namesがでてきますが、これはなんなのかというと、ExcelというApplicationのライブラリに定義されているNamesクラスなわけです。

オブジェクトブラウザーで見ると、Excelライブラリとなっています。

f:id:shinmai_papa:20210320173521p:plain

そしてこのNamesオブジェクトはどこに紐づいているかというと、Workbookオブジェクトまたは、Worksheetオブジェクトです。

f:id:shinmai_papa:20210320173525p:plain

これは、名前の定義を新規に作成するときを思い出してもらえれば納得してもらえると思います。

f:id:shinmai_papa:20210320173529p:plain

範囲でブックかシートを選択するところです。

ここでの選択結果によって、Worksheetに紐づくか、Workbookに紐づくか決まります。

VBAで細かい制御をするときは意識されるといいかと思います。

終わりに

名前の定義を削除するコードの紹介でした。

この名前の定義って便利なんですが、予想外のところでいたずらするので、私はなるべく使いたくない・・・

といってもワークシート関数の可読性をあげたりできるところは評価できるのですが・・・

うーん、便利だけど使うか否か。

これはExcelで運用している以上、永遠につきまとう課題ですね。

というわけでここまでお読みいただき、ありがとうございました。