【Excel VBA】名前定義の表示・削除

Excelでシートコピーすると名前重複のダイアログが出て…というよくある話

表示されていない（非表示の）名前定義を表示するところから始まり、
参照エラーの名前定義など条件付きで削除するマクロを作成・使用してみましたが、
結局ほぼすべて削除だなというところに至りました。

せっかく作ったので、途中で作った条件付き削除のマクロなども載せておきます。

## 名前定義の表示・非表示

2 → 3 → 1 の順で作成。

### 1. FilterDatabase以外を表示

FilterDatabaseは削除するとフィルタが壊れるので、非表示のまま

```vb:FilterDatabase以外を表示
Public Sub showNames()
'
' 非表示の名前定義を表示する
' FilterDatabaseは表示しない
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
        If nm.name Like "*!_FilterDatabase" Then
            ' FilterDatabaseは表示しない
        ElseIf nm.Visible = False Then
            nm.Visible = True
        End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub

```

### 2. 全て表示

非表示になっているものをとりあえず全部表示

```vb:全て表示
Public Sub showAllNames()
'
' 非表示の名前定義を表示する
'
' 参考　https://dev.classmethod.jp/tool/excel-delete-name/
'
    Dim name As Object
    For Each name In Names
        If name.Visible = False Then
            name.Visible = True
        End If
    Next
    MsgBox "完了", vbOKOnly
End Sub
```

### 3. FilterDatabaseを非表示

（フィルタを壊して）やっぱりFilterDatabaseは非表示に

```vb:FilterDatabaseを非表示
Public Sub hideNameFilterDB()
'
' 名前定義FilterDatabaseを非表示にする
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
        If nm.name Like "*!_FilterDatabase" Then
            nm.Visible = False
        End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub
```


## 名前定義の削除

### 除外対象以外の名前定義を削除 

除外対象
* フィルタ関連（FilterDatabase）
* 印刷設定関連（Print_Area、Print_Titles）

```vb:除外対象以外を削除 
Public Sub delNames()
'
' 名前定義を削除する
' 以下の除隊対象は削除しない
' * FilterDatabase
' * Print_Area
' * Print_Titles
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
        If nm.name Like "*!_FilterDatabase" _
        Or nm.name Like "*!Print_Area" _
        Or nm.name Like "*!Print_Titles" Then
            ' 除外対象の場合は処理しない
        Else
            nm.Delete
        End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub

```

:::note info
テーブル名は名前定義ではない

Excelの名前の管理ではテーブル名も含めて表示されますが、
テーブル名は名前定義 `Names` には含まれません。
これを実行するとテーブルも削除されるのではと心配していましたが、削除されません。
:::

### エラーの名前定義を削除 
`RefersTo` （参照先の文字列）に、#REF! か #N/A が含まれる名前定義を削除
（これも）2 → 3 → 1 の順で作成。

* エラー削除1
```vb: エラー削除1：#REF! または #N/A 
Public Sub delErrorNames()
'
' エラーの名前定義を削除
'
    Dim nm As name
    On Error Resume Next
    For Each nm In names
        If nm.RefersTo Like "*[#]REF!*" _
        Or nm.RefersTo Like "*[#]REF'!*" _
        Or nm.RefersTo Like "*[#]N/A*" Then
            nm.Delete
        End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub
```

エラー削除2の条件 `nm.RefersTo Like "*[#]REF!*"` では
`'C:\file path\[book.xls]#REF'!$A$1` というような参照範囲が対象にならなかったため、
`nm.RefersTo Like "*[#]REF'!*"` を削除するパターンに追加。

* エラー削除2

```vb: エラー削除2：#REF!
Public Sub delErrorNamesREF()
'
' エラーの名前定義を削除
'
' 参考　https://excel-ubara.com/excelvba5/EXCELVBA214.html
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
       If nm.RefersTo Like "*[#]REF!*" Then
           nm.Delete
       End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub
```
* エラー削除3
```vb: エラー削除3：#N/A
Public Sub delErrorsNamesNA()
'
' エラーの名前定義を削除
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
       If nm.RefersTo Like "*[#]N/A*" Then
           nm.Delete
       End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub
```

### 別ブック参照の名前定義を削除 

`RefersTo` （参照先の文字列）に、.xls と ! が含まれる名前定義を削除
自ブックに、.xls を含むシート名があり、そのシート内の範囲を定義していると
それも削除対象になると思います。

```vb
Public Sub delOtherBookNames()
'
' 別のExcelを参照している名前定義を削除
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
        If nm.RefersTo Like "*.[xX][lL][sS]*!*" Then
            nm.Delete
        End If
    Next
    On Error GoTo 0
    MsgBox "完了", vbOKOnly
End Sub
```

### 表示されている名前定義を削除

作ってみたけどあまり使うことはなさそうだなとなったやつ。
表示されているものは名前の管理から削除できるので…。
参考に残しておきます。

```vb:表示されている名前定義を削除
Public Sub delVisibleNames()
'
' 表示されている名前定義を削除する
'
    Dim nm As name
    On Error Resume Next
    For Each nm In Names
        If nm.Visible = True Then
            nm.Delete
        End If
    Next
    MsgBox "完了", vbOKOnly
End Sub
```

## 参考ページ
（一部のソース内にも入ってますが）作成時に参考にしたページ

* [[Excel] シートで非表示になっている名前の定義を消す方法](https://dev.classmethod.jp/articles/excel-delete-name/)
* [名前定義の一覧と削除（Name）| VBAサンプル集](https://excel-ubara.com/excelvba5/EXCELVBA214.html)