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

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-22

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


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


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

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。


出題

出題ツイートへのリンク

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

マクロ VBA 100本ノック


サンプルファイルです。
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?」で削除エラーを再現しました。

マクロ VBA 100本ノック

_xlfn.UNICODE
参照範囲=#NAME?
この名前定義はVBAで削除できません。
_xlfn.○○○
これについては、特に削除しなければならないものではありませんが、
VBAでDeleteがエラーになるサンプルとして使いました。

マクロ VBA 100本ノック

このようなエラーが発生します。
エラーの原因については、メッセージの通りですが
そもそも規則に反する名前が入っていることが問題な気もしますが・・・
とにかく、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形式に変換


新着記事NEW ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


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

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




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


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



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