VBA100本ノック 18本目：名前定義の削除
名前定義を削除する問題です。
名前定義から、非表示なっているものを表示し、参照不可になっている名前定義は削除します。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
出題
出題ツイートへのリンク
#VBA100本ノック 18本目
不明な名前定義が沢山あるので、以下の処理をしてください。
・非表示の名前定義は表示にする
・参照範囲に「#REF!」が含まれる場合は削除しイミディエイトに「名前」と「参照範囲」を出力
・最後に非表示件数と削除件数をメッセージボックスに表示
※ブック指定は任意
サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_18.xlsm
https://excel-ubara.com/vba100sample/VBA100_18.zip
不明な名前定義が沢山あるので、以下の処理をしてください。
・非表示の名前定義は表示にする
・参照範囲に「#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!"が含まれている場合に名前定義を削除しています。
名前定義にはVBAでは削除できないものがあり、Deleteがエラーになる場合があります。
VBAで削除できなくても、ダイアログでは大抵の場合削除できるはずなので、手動で削除してください。
エラー対応したVBAは記事補足に記載しました。
参照範囲の数式は、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で削除できなくても、ダイアログでは大抵の場合削除できるはずなので、手動で削除してください。
エラー対応したVBAは記事補足に記載しました。
補足
VBAで名前定義を削除した時、エラーとなってしまうものがあります。
VBAで削除できない名前定義が作成されてしまう原因はいろいろあります。
フィルター等のエクセル機能で自動作成されるものはVBAでは削除できないものが多くあります。
（ただし普通は、不要になった時点でエクセルが名前定義を削除します。）
また、古いExcelのバージョンで作成したファイルを使い回したものには、今の名前定義で許可されない名前が入っていたりします。
フィルター等のエクセル機能で自動作成されるものはVBAでは削除できないものが多くあります。
（ただし普通は、不要になった時点でエクセルが名前定義を削除します。）
また、古いExcelのバージョンで作成したファイルを使い回したものには、今の名前定義で許可されない名前が入っていたりします。
お題では「#REF!」についての削除でしたが、
簡単に作成できる別のエラー「#NAME?」で削除エラーを再現しました。
_xlfn.UNICODE
参照範囲=#NAME?
この名前定義はVBAで削除できません。
簡単に作成できる別のエラー「#NAME?」で削除エラーを再現しました。
参照範囲=#NAME?
この名前定義はVBAで削除できません。
_xlfn.○○○
これについては、特に削除しなければならないものではありませんが、
VBAでDeleteがエラーになるサンプルとして使いました。
これについては、特に削除しなければならないものではありませんが、
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本ノック」の記事
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
16本目：無駄な改行を削除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
17本目：重複削除（ユニーク化）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
18本目：名前定義の削除19本目：図形のコピー
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
20本目：ブックのバックアップ・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
21本目：バックアップファイルの削除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
22本目：FizzBuzz発展問題・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
23本目：シート構成の一致確認・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
24本目：全角英数のみ半角・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
25本目：マトリックス表をDB形式に変換・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 18本目：名前定義の削除
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。