Excelの名前定義をVBAで一括解除する【実務で活用できる】

Excelに大量に残っている使われていない「名前の定義」で困っていませんか?
前々回の記事で「非表示シート」の削除を紹介しましたが、削除したシートで設定した名前定義がそのまま残っている場合、定義が壊れてエラーとなっている場合があります
前々回記事はこちらを参照してください

使っていない「名前定義」をいちいち削除するのって結構面倒なんですよね(^^;

今回は、この結構面倒を起こす「名前定義」を何とかしてみたいと思います

この記事は不要な「名前定義」をVBAで一括削除する方法について解説していきます
「名前重複エラー」に悩まされている方は是非ご覧ください

Excelの「名前定義」が増えてしまう原因は

Excelの「名前定義」とは

「名前定義」とは、セル範囲を座標で指定するのではなく、その範囲に「名前」を付けて座標の変わりに使うことができるようにするものです
Excelで名前を付ける手順は、名前を付けたいセル範囲を選択して、数式バーの左にある「名前ボックス」に任意の名前を入力すればOK簡単です。もちろん「名前の管理(Ctrl+F3)」や「名前定義」でも作成できます

代表的な「名前定義」は、「印刷範囲」です。「印刷範囲」は印刷を実行すると勝手に「Print_Area」という名前が作成されます

セル範囲「=’Sheet2′!$K$1:$L$60」を「範囲k」という名前で設定しておけば、VLOOKUP関数の場合
通常にセルに記述する場合は、=Vlookup(A1,’Sheet2′!$K$1:$L$60,2,0) です
定義してある「名前」を使用した場合は、=Vlookup(A1,範囲k,2,0) と記述できます

Excelの「名前定義」が大量にできてしまう原因

「名前」の使用はこのように便利なのですが、人からもらったブックなどに全然使われていない名前が大量に残っていて邪魔だったりすることがよくあります
「名前定義」が増えてしまう原因はいろいろあるようですが、最初は個人的なテンプレートとして定義した「名前」が沢山の人や沢山の部署で使いまわされ、コピーが何度も繰り返された結果、使っていない「名前」を大量に発生させているものと思われます

試してみてください。同じブック内でシートをコピーすると範囲はコピーしたシートで、同じ名前の定義が自動的にコピー作成されます。コピー前に10個あったものならば倍の20個の名前が定義されています。このように意図せずに「名前定義」増幅させているのです

Excelの「名前定義」を手動で消す

「名前」を削除したい場合は、「数式タブ」⇒「定義された名前」⇒「名前の管理」をクリックすると「名前の管理ダイアログ(ショートカット= Ctrl+F3)」が表示されるので、対象の名前を選択⇒削除ボタン、で削除できます(ただし非表示の名前は消すことが出来ません)

これ、数個程度ならこの操作でいいのですが、何百個(もしかしたら何千何万個)になるとウンザリしてきますし、効率が悪くて仕事になりません

実際に2万個を超える「名前定義」に何度か出くわした経験があります(^^;

Excelの「名前定義」をVBAで一括削除しよう

ここはやはり、VBAにお願いした方がよさそうです

「名前定義」削除のロジックをフローチャートで確認

クリックで拡大できます

「名前の重複」解除の設定は盛り込んでいません(^^;

次の設定シートを用意します

クリックで拡大できます

・上の画像のとおり実行前に【削除対象外】にする定義があれば指定しておきます
・指定場所はK4以下に記載します。J列に「1」を指定しなければ対象外になりません
・何も指定しなければすべての定義を削除します(最後に保存するかどうか確認します)
こんな感じです(^^)

Excelの「名前定義」一括削除するコード

「名前定義」一括削除の開始コード

Option Explicit
Private Declare Function GetInputState Lib "USER32" () As Long 'DoEventsの代わり
Public DelCount As Long '削除数カウント用
Public NameCount As Long '名前定義数保存用
'名前定義の削除処理開始
Sub StartDelNamesDef()
    Dim selectFileName As Variant
    
    'ファイル選択ダイアログを表示
    selectFileName = _
        Application.GetOpenFilename( _
            FileFilter:="Microsoft Excel ブック,*.xls?", _
            Title:="ファイルを選択してください(複数可)", _
            MultiSelect:=True)
    If IsArray(selectFileName) = False Then _
                MsgBox ("未選択のため処理を中止します!"): Exit Sub
    'チラつきをなくすためExcelインスタンスで対応
    Dim f As Variant    'ファイル用
    Dim tgwb As Object
    Dim owb As Excel.Workbooks
    Dim oxl As Excel.Application
    Dim rc As Long
        
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "処理中...."
    Set oxl = CreateObject("Excel.Application") 'インスタンス
    Set owb = oxl.Application.Workbooks 'WBオブジェクト
    '選択されたファイルに対する処理
    On Error Resume Next
    For Each f In selectFileName
        If GetInputState() Then DoEvents    '重いファイル用対策
        'ブックを開く
        Set tgwb = owb.Open(Filename:=f, UpdateLinks:=0)
        'ここから開いたブックに対する処理
        Application.StatusBar = tgwb.name & " の処理中...."
        With tgwb
           DelCount = 0    'カウンター初期化
            '非表示になっている名前定義を表示させる
            Call VisibleNames
            '削除処理はこちら
            Call DeleteNamesDefinition
            '最終確認
            rc = MsgBox(.name & "を保存しますか?" & vbCrLf _
                    & "【削除件数は " & DelCount & "件でした】" & vbCrLf _
                    & " はい(Y):保存して終了(元には戻せません)" & vbCrLf _
                    & " いいえ(N):保存せずに終了(削除前のまま)", _
                    vbYesNo + vbExclamation, "保存前の確認!")
            If rc = vbYes Then
                .Close Savechanges:=True
            Else
                .Close Savechanges:=False
            End If
        End With
        Set tgwb = Nothing
    Next
    Set owb = Nothing
    Set oxl = Nothing
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub

・「3~4行目」件数をカウントするための変数をPublicで用意しています
今回も複数ファイル処理でちらつきが発生するのでインスタンス化しています
・「39行目」で非表示の「名前定義」を表示させる処理呼び出し「Call VisibleNames」
・「41行目」で実際の「名前定義」実行処理呼び出し「Call DeleteNamesDefinition」
・「43行目」でブック名と削除処理件数をメッセージボックスに表示して保存するかしないかの最終確認をしています
・「24~25行目」は自動計算をストップ、ステータスバー表示を有効にしています

「名前定義」を削除する処理コード

'名前定義を削除する処理
Sub DeleteNamesDefinition()
    Dim objName As name 'Nameオブジェクト
    Dim sh As Worksheet
    Dim rn As Range '対象外設定範囲
    Dim saveName() As String '対象外設定保存用
    Dim i As Long   '対象外の設定数
    Dim n As Long   '対象外読み込み用カウンター
    Dim r As Range  '対象外設定用フラグ
      
    Set sh = ThisWorkbook.Sheets("名前定義削除")
    With sh
        Set rn = .Range("J4", .Cells(Rows.Count, 10).End(xlUp))
        i = Application.CountIf(rn, 1) '「1」をカウント
        ReDim saveName(i - 1)   '配列数を設定
        n = 0
        For Each r In rn
            If r.Value = 1 Then
                saveName(n) = .Cells(r.Row, 11).Value
                n = n + 1
            End If
        Next
    End With
    NameCount = ActiveWorkbook.Names.Count '開いたブックの名前定義数
    'Nameコレクション内のNameオブジェクト分をループ処理
    For Each objName In ActiveWorkbook.Names
        'エラーが発生時は次のオブジェクトに飛すため
        On Error GoTo ERR_Resume
        '削除したくない名前定義を飛ばす
        If (objName.Value Like "*[#]REF*") Then '参照エラーは全て削除
            objName.Delete
            DelCount = DelCount + 1 'カウントアップ
        Else
            For i = 0 To n
                If (objName.name Like saveName(i)) Then 'これは削除しない
                Else '対処外設定以外は削除
                    objName.Delete
                    DelCount = DelCount + 1 'カウントアップ
                End If
            Next i
        End If
        Application.StatusBar = "処理中....削除件数:" & DelCount & "/" & NameCount
ERR_Resume:
    '何もせず次へ
    Next objName
End Sub

・「17~23行」で、対象外とする定義設定をセルから配列に読み込んでいます
・「26~45行目」のループで対象外かどうかの判定をして
「37行目」で削除対象の「名前定義」を削除していま
・「32と38行目」で処理した件数をカウントアップしています
・「42行目」は、ステータスバーの処理件数を更新しています

非表示の「名前定義」を表示させるコード

'非表示になっている名前定義を表示させる
Sub VisibleNames()
    Dim name As Object
    'Namesコレクション内のNameオブジェクト分をループ処理
    For Each name In Names
        If name.Visible = False Then
            name.Visible = True
        End If
    Next
End Sub

・これは説明の必要は無いですね
・nameオブジェクトが「非表示」だったら「表示」に変えているだけですね

まとめ(おわりに)

まとめと感想など

・シートをコピーするときなどに「名前の重複」メッセージが表示されたときは、異常があるということなので、後回しにせずに対処しておいた方がよさそうです
・そんな時、このツールを活用すればまとめて複数ファイルをクリンナップできます

記事内で紹介したコードの実行は、必ずバックアップを取ってから行ってください
大量な「名前定義」は意外とファイルサイズを肥大化させます
・不要な「名前定義」はこまめに削除することをお勧めします
・特に、自分以外の人にファイルを配布する時には必ずチェックして配布しましょう
・また、提供いただいたファイルのチェックもしておけば安心ですね(^^)

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
是非!サンプルファイルをダウンロード出来ますのでそのまま使ってみてください(^^)/


【今後の記事内容はどうしようかなぁ・・・】
・記事関連で「条件付き書式の削除」についても検討していきます
・今までに紹介した記事で使用したコードの改変も検討します
・高速化した「VlookUp関数」のVBAでの活用法を再検討してみたいと思います
・その他「小ネタいろいろ」などなど・・・・・
・今後これらのどれかについて記事にしていきたいと思います。ご期待ください(^^)/

サンプルファイルをダウンロードできます(下記リンク先へ)

記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください