非表示の名前定義

前回に引き続きエクセルの神髄(@yamaoka_ss)さんが出題されている、#VBA100本ノックである。
今回は下記の問題にチャレンジした。

#VBA100本ノック 18本目
不明な名前定義が沢山あるので、以下の処理をしてください。
・非表示の名前定義は表示にする
・参照範囲に「#REF!」が含まれる場合は削除しイミディエイトに「名前」と「参照範囲」を出力
・最後に非表示件数と削除件数をメッセージボックスに表示
※ブック指定は任意 pic.twitter.com/AbpkbaBIBR

— エクセルの神髄 (@yamaoka_ss) 2020年11月6日

自分の解答

Sub VBA100ノック18()
     Dim 名前定義 As Name
     Dim 非表示件数 As Long
     Dim 削除件数 As Long
     
     For Each 名前定義 In ActiveWorkbook.Names
          If 名前定義.Visible = False Then
               名前定義.Visible = True
               非表示件数 = 非表示件数 + 1
          End If
          
          If 名前定義.Value Like "*![#]REF!*" Then
               Debug.Print 名前定義.Value & ":" & 名前定義.RefersTo
               名前定義.Delete
               削除件数 = 削除件数 + 1
          End If
     Next
     MsgBox "非表示件数:" & 非表示件数 & vbCrLf & "削除件数:" & 削除件数
End Sub

神髄さんのお手本コード

Sub VBA100_18_01()
　　Dim visibleCnt As Long
　　Dim deleteCnt As Long
　　Dim nm As Name
　　
　　For Each nm In ActiveWorkbook.Names
　　　　If Not nm.Visible Then
　　　　　　visibleCnt = visibleCnt + 1
　　　　　　nm.Visible = True
　　　　End If
　　　　If InStr(nm.RefersTo, "#REF!") > 0 Then
　　　　　　deleteCnt = deleteCnt + 1
　　　　　　Debug.Print nm.Name & ":" & nm.RefersTo
　　　　　　nm.Delete
　　　　End If
　　Next
　　
　　MsgBox "非表示件数：" & visibleCnt & vbLf & _
　　　　　 "削除件数：" & deleteCnt
End Sub

名前定義を処理するようなことが今迄に無かったのでコード自体ももちろん勉強になったのだが、今回印象に残ったのは「非表示の名前定義」の存在である。

非表示になる名前定義とは

次のような場合には非表示の名前定義が存在するようです

• オートフィルタを設定する
• 古いVerのExcelファイルを新しいVerのExcelで開く

他にも色々ありそうですが、これらが原因で作成された名前定義はVBA使用しなければ、名前の管理メニューに表示されず、認識することが出来ません。

非表示の名前定義が存在してるかどうか確認したい場合は、上記コードに含まれている下記のコードで表示することが出来ます。

名前定義オブジェクト.visible = True

表示された名前定義の中にエラーが発生しているものや、明らかに不要なものがあれば削除しておきましょう。