Excelの名前の定義をVBAで削除する際に発生するエラーの対処

名前の定義をVBAで削除する際、通常設定できない名前の定義が邪魔している可能性があります。

やりたいこと

VBAを使用してExcelの名前の定義を削除する際、エラーが発生して削除できないケースがあります。

これは「設定できない名前の定義名」を削除する際に例外エラーが発生している可能性が高いです。

ここではすべての名前の定義を削除するために

「設定できない名前の定義名」を削除する方法をご紹介いたします。

大まかにやりたいことを箇条書きしています。

名前の定義を削除する際に以下のフローで処理を行うとすべての名前の定義を一掃できます。
- 隠された名前の定義をすべて表示
- Excelの参照形式を変更する[A1]↔[R1C1]
- 名前の重複ダイアログの処理
- 名前の定義の削除
- Excelの参照形式をもとに戻す

隠された名前の定義をすべて表示

Sub visibleName()
    Dim n   As Name
    'エクセルの隠された名前の定義をすべて表示します
    For Each n In ActiveWorkbook.Names
        n.Visible = True
    Next
End Sub

Excelの参照形式を変更する[A1]↔[R1C1]

なぜ、名前の定義削除をする際にExcelの参照形式を変更する必要があるかというと、

設定できない名前の定義が設定されている場合、

参照形式を変更した際に「名前の重複」というダイアログが表示され、

使用可能な名前に変更するように促されます。

現状、設定できない名前の定義をVBAで削除するには

この「名前の重複」ウィンドウを利用する方法しか私は知りません。

Private Sub changeExcelRefStyle() 
    Dim beforeReferenceStyle
    'もともと設定されているExcel参照形式を保持しておく
    beforeReferenceStyle = Application.ReferenceStyle
    'R1C1の場合A1に変更、A1の場合はR1C1に変更
    If beforeReferenceStyle = xlR1C1 Then
        Application.ReferenceStyle = xlA1
    Else
        Application.ReferenceStyle = xlR1C1
    End If
End Sub

名前の重複ダイアログの処理

user32.dllを使用してプロセス割り込みが行えるようにします。

内容としては、VBAでシステム割り込み許可している間に「名前の重複」ウィンドウが表示されたら

ランダムに適当な文字列を入力して名前の定義名を更新するというものです。

Private Sub TimerProc()
    Dim hwnd As Long
    hwnd = FindWindow("bosa_sdm_XL9", "名前の重複")
    If hwnd > 0 Then
        SendKeys getRandStr(3, 20), 10
        SendKeys "{ENTER}"
    End If
End Sub
 
Private Function getRandStr(min As Long, max As Long) As String
    Dim s As String
    Dim i As Long
    max = Int(max * Rnd)
    For i = 0 To min + max
        Randomize
        s = s & Chr(65 + Int(26 * Rnd))
    Next
    getRandomString = s
End Function

名前の定義の削除

Sub delDefinedNames()
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        On Error Resume Next  ' エラーを無視
        n.Delete
    Next
End Sub

名前の定義を削除します。

念のため、エラーを無視するように「On Error Resume Next」を使用しています。

実際に使用するコードのイメージ

予め言っておきますが、利用については個人の責任でお願いします。

user32.dllを使用する関係上「標準Module」としてVBAを作成してください。

Public Declare Function SetTimer Lib "user32.dll" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32.dll" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String,ByVal lpWindowName As String) As Long
Sub main()
    '名前の定義をすべて表示する
    Call visibleName
    'プロセスの割り込み開始
    Dim timerID As Long
    timerID = SetTimer(0, 0, 100, AddressOf TimerProc)
    Dim beforeReferenceStyle
    'Excel参照形式を変更する
    beforeReferenceStyle = changeExcelRefStyle(beforeReferenceStyle)   
    
    '名前の定義の削除
    call delDefinedNames
    
    '元の参照形式に変更する
    Application.ReferenceStyle = beforeReferenceStyle
 
    'プロセスの割り込み終了
    KillTimer 0, timerID
End Sub
Sub visibleName()
    Dim n   As Name
    'エクセルの隠された名前の定義をすべて表示します
    For Each n In ActiveWorkbook.Names
        n.Visible = True
    Next
End Sub
Sub changeExcelRefStyle(beforeReferenceStyle)  
    'もともと設定されているExcel参照形式を保持しておく
    beforeReferenceStyle = Application.ReferenceStyle
    'R1C1の場合A1に変更、A1の場合はR1C1に変更
    If beforeReferenceStyle = xlR1C1 Then
        Application.ReferenceStyle = xlA1
    Else
        Application.ReferenceStyle = xlR1C1
    End If
End Sub
Sub delDefinedNames()
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        On Error Resume Next  ' エラーを無視
        n.Delete
    Next
End Sub
Private Sub TimerProc()
    Dim hwnd As Long
    hwnd = FindWindow("bosa_sdm_XL9", "名前の重複")
    If hwnd > 0 Then
        SendKeys getRandStr(3, 20), 10
        SendKeys "{ENTER}"
    End If
End Sub
 
Private Function getRandStr(min As Long, max As Long) As String
    Dim s As String
    Dim i As Long
    max = Int(max * Rnd)
    For i = 0 To min + max
        Randomize
        s = s & Chr(65 + Int(26 * Rnd))
    Next
    getRandomString = s
End Function