VBA100本ノック 18本目：名前定義の削除

最終更新日：2021-02-22

VBA100本ノック 18本目：名前定義の削除

名前定義を削除する問題です。
名前定義から、非表示なっているものを表示し、参照不可になっている名前定義は削除します。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

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

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_18.xlsm
https://excel-ubara.com/vba100sample/VBA100_18.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

名前定義には非表示になっているものがあります。これはVisibleプロパティで判断できます。
参照範囲の数式は、RefersToやRefersToLocal、またValueでも取得できます。
この式に"#REF!"が含まれている場合に名前定義を削除しています。


  
    
      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

名前定義にはVBAでは削除できないものがあり、Deleteがエラーになる場合があります。
VBAで削除できなくても、ダイアログでは大抵の場合削除できるはずなので、手動で削除してください。
エラー対応したVBAは記事補足に記載しました。

補足

VBAで名前定義を削除した時、エラーとなってしまうものがあります。

VBAで削除できない名前定義が作成されてしまう原因はいろいろあります。
フィルター等のエクセル機能で自動作成されるものはVBAでは削除できないものが多くあります。
（ただし普通は、不要になった時点でエクセルが名前定義を削除します。）
また、古いExcelのバージョンで作成したファイルを使い回したものには、今の名前定義で許可されない名前が入っていたりします。

お題では「#REF!」についての削除でしたが、
簡単に作成できる別のエラー「#NAME?」で削除エラーを再現しました。

_xlfn.UNICODE
参照範囲=#NAME?
この名前定義はVBAで削除できません。

_xlfn.○○○
これについては、特に削除しなければならないものではありませんが、
VBAでDeleteがエラーになるサンプルとして使いました。

このようなエラーが発生します。
エラーの原因については、メッセージの通りですが
そもそも規則に反する名前が入っていることが問題な気もしますが・・・
とにかく、VBAがエラーになってしまうので、これに対応したVBAが以下になります。


  
    
      Sub VBA100_18_02()

　　Dim visibleCnt As Long

　　Dim deleteCnt As Long

　　Dim errorCnt As Long

　　Dim nm As Name

　　

　　On Error Resume Next

　　

　　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 Or _

　　　　　 InStr(nm.RefersTo, "#NAME?") > 0 Then

　　　　　　deleteCnt = deleteCnt + 1

　　　　　　Debug.Print nm.Name & ":" & nm.RefersTo

　　　　　　Err.Clear

　　　　　　nm.Delete

　　　　　　If Err Then

　　　　　　　　errorCnt = errorCnt + 1

　　　　　　　　Debug.Print "↑「削除失敗」手動削除願います。"

　　　　　　End If

　　　　End If

　　Next

　　

　　MsgBox "非表示件数：" & visibleCnt & vbLf & _

　　　　　 "削除件数　：" & deleteCnt & vbLf & _

　　　　　 "内削除失敗：" & errorCnt

End Sub

InStr(nm.RefersTo, "#REF!") > 0
これは、Likeで書く場合は「#」がワイルドカードになるので、以下のように書く必要があります。
If nm.RefersTo Like "*[#]REF!*"

サイト内関連ページ

名前定義｜エクセル入門

・名前ボックス・名前の管理・定義済の名前定義の呼び出し

第92回．名前定義（Names）｜VBA入門

・Namesコレクション・Nameオブジェクト・RangeオブジェクトのNameプロパティ・名前定義を使ったRangeの書き方・シートコピー時は名前定義に注意・名前定義の実践例

名前定義の一覧と削除（Name）｜VBAサンプル集

・名前定義の一覧を取得し、シートに書き出すマクロVBA ・非表示の名前定義を表示・サイト内の関連ページ

指定セルに名前定義されているか判定する｜VBAサンプル集

・指定セル範囲が何らかの名前定義に含まれているか・指定セル範囲と同一範囲の名前定義があるか・すでに設定されている名前定義を削除してから再設定・マクロVBAでの名前定義について

同じテーマ「VBA100本ノック」の記事

15本目：シートの並べ替え
 16本目：無駄な改行を削除
 17本目：重複削除（ユニーク化）
18本目：名前定義の削除
19本目：図形のコピー
 20本目：ブックのバックアップ
 21本目：バックアップファイルの削除
 22本目：FizzBuzz発展問題
 23本目：シート構成の一致確認
 24本目：全角英数のみ半角
 25本目：マトリックス表をDB形式に変換

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
18本目：名前定義の削除

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 18本目：名前定義の削除