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

VBAを100本の練習問題で鍛えます
最終更新日:2020-11-07

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


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


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


出題

出題ツイートへのリンク

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

マクロ VBA 100本ノック


頂いた回答

解説

名前定義には非表示になっているものがあります。これは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!*"


サイト内関連ページ

名前定義|エクセル入門
セル範囲や数式に名前を付けることができます。この名前定義は、エクセルで複雑な設定をしたい場合には必須の技術になります。名前ボックス このA1等のセル番地が表示されている部分を「名前ボックス」と呼びます。
第92回.名前定義(Names)|VBA入門
名前定義をマクロVBAで扱う場合の解説になります、名前定義は、複数セル範囲や単一セルに対して名前を付けることで、そのセル範囲を参照する時に名前で参照できるようにするものです。名前で参照できることで、セル位置(行位置、列位置)を固定値で指定しなくて済むようになります。
名前定義の一覧と削除(Name)|VBAサンプル集
名前定義は使い方によっては、とても便利な機能ですが、長く使っているブックでは、とても多くの名前定義が入ってしまっていたり、参照エラーを起こしている名前定義が多数あったりと、管理に困る場合も多々出てきます。これらが発生する原因としては、ブック間のシートコピーで増えていってしまったり、シートおよびセルの削除によって参照エラーになったままにしておくことで、
指定セルに名前定義されているか判定する|VBAサンプル集
名前定義は、VBAでは、セル位置の特定において重要な役割を持ちます、あるセルが名前定義されているか判定するVBAになります。ここでは、名前定義されている場合は、その名前定義を削除するVBAサンプルとしています。指定セル範囲が何らかの名前定義に含まれているか Subsample1(rngAsRange) DimnmAsName ForEachnmInNam…




同じテーマ「Python入門」の記事

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


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

VBA100本ノック 34本目:配列の左右回転|VBA練習問題(11月28日)
VBA100本ノック 33本目:マクロ記録の改修|VBA練習問題(11月26日)
VBA100本ノック 32本目:Excel終了とテキストファイル出力|VBA練習問題(11月25日)
VBA100本ノック 31本目:入力規則|VBA練習問題(11月24日)
将棋とプログラミングについて~そこには型がある~|エクセル雑感(11月22日)
VBA100本ノック 30本目:名札作成(段組み)|VBA練習問題(11月22日)
VBA100本ノック 29本目:画像の挿入|VBA練習問題(11月21日)
VBA100本ノック 28本目:シートをブックに分割|VBA練習問題(11月19日)
VBA100本ノック 27本目:ハイパーリンクのURL|VBA練習問題(11月18日)
VBA100本ノック 26本目:ファイル一覧作成|VBA練習問題(11月17日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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