マクロを使用して特定の値を含んだ名前の定義を一括削除したいです

Question

前提・実現したいこと

初めてマクロを書いているので理解不足な点が多々あることはご了承ください。
大量のexcelファイルを一つのフォルダに格納して、
該当の文字列を含んだ名前の定義（値、参照範囲どちらかに含んでいるもの）を削除するマクロを作成したいです。
その中で、非表示となっている名前の定義も削除対象としたいです。

発生している問題・エラーメッセージ

getName(nm.Name) = 1 _部分でrangeのglobalエラーが表示されてしまっています。

該当のソースコード

Public Sub DeleteNames()
    '非表示となっている名前の定義を表示
    DisplayNames
    Dim nm As name
    Dim i As Integer: i = 0
    On Error Resume Next  ' エラーを無視。(削除件数にカウントしてしまう)
    '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除
    
    For Each nm In ActiveWorkbook.Names
    If InStr(nm.Value, "#REF") > 0 OR _
       getName(nm.Name) = 1 _
       Then
       nm.Delete
       i = i + 1
    End If
    Next nm
    
End Sub

'非表示の名前の定義を再表示する
Public Function DisplayNames()
    Dim name As Object
    For Each name In Names
        If name.Visible = False Then
           name.Visible = True
        End If
    Next
End Function

'セル範囲の値を順番に取得し、CheckRangeValueを呼び出す
Public Function getName(na As String)
  Dim i As Long
  Dim naStr As String
  Dim result As Integer
  result = 0
  
  For i = 1 To Range(na).Count
    naStr = Range(na).Item(i).Value
    
    If CheckRangeValue(naStr) = 1 Then
        result = 1
    End If
  Next i
  getName = result

End Function

'名前の定義の「値」に特定の文字列を含んでいた場合1を返却
Public Function CheckRangeValue(r As String)
    Dim val
    Dim result As Integer
        If InStr(val.Value, "#REF") > 0 _
        Then
            result = 1
        End If
    CheckRangeValue = result
End Function

試したこと

処理の中にdebugを張ったりしてみましたが、名前の定義の名前部分が正しく取得できていることは確認できました。

補足情報（FW/ツールのバージョンなど）

特になし

Comments

[退会済みユーザー]

まず人に見てもらうならインデントしましょう。次にDeleteNames()のIf InStr(nm.Value, "#REF") > 0 _ getName(nm.Name) = 1 _ Thenですが、Orが抜けているのが原因と思われます。さらにrestult = 1はスペルが違いますね。　CheckRangeValue(r As String)のrはFor Eachで使っていることからAs Rangeではないでしょうか。　この他にもエラーの原因が多数ありそうです。デバッグ中はOn Errorは切ったほうが良いと思います。最初のREF判定とgetNameをOrでつなぐのではなく、別のIfで括ったほうが理解しやすいかと思います。時間があればデバッグしてみます。

[imihito]

ソースコードの直前の行に「```vba」、直後の行に「```」を追加して、ソースコードにシンタックスハイライトが適用されるようにしてください

Answer 1

On Error Resume Next ' エラーを無視。(削除件数にカウントしてしまう)

先に書いたように、まずコレは消しましょう。無い状態で動くように書くべきです。

該当の文字列を含んだ名前の定義（値、参照範囲どちらかに含んでいるもの）

参照範囲の中のセルに少しでも#REFがあったら名前定義の削除対象にすると解釈しましたが合ってますか？

それと関数を細かく細分化されていますが、この程度の処理なら分ける必要は無いように思います。

これを踏まえてコードを整理してみましたが如何でしょうか。

Option Explicit

Public Sub DeleteNames()
    Const DelStr = "#REF" '該当の文字列

    Call DisplayNames '非表示となっている名前の定義を表示
    
    Dim nm As Name
    Dim i As Long: i = 0
    'On Error Resume Next  ' エラーを無視。(削除件数にカウントしてしまう)
    '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除

    For Each nm In ActiveWorkbook.Names
        If InStr(nm.Value, DelStr) > 0 Then
            'nm.Delete
            Debug.Print nm.Name & "を削除しました。"
            i = i + 1
        Else
            If getName(nm, DelStr) Then
                'nm.Delete
                Debug.Print nm.Name & "を削除しました。"
                i = i + 1
            End If
        End If
    Next nm
End Sub

'非表示の名前の定義を再表示する
Public Function DisplayNames()
    Dim Name As Object
    For Each Name In Names
        If Name.Visible = False Then
           Name.Visible = True
        End If
    Next
End Function

'名前定義の範囲に特定文字列sが存在するか
Public Function getName(nm As Name, s As String) As Boolean
    Dim R As Range
    For Each R In nm.RefersToRange
        If InStr(R.Formula, s) > 0 Then
            getName = True
        ElseIf InStr(R.Value, s) > 0 Then
            getName = True
        End If
    Next
End Function

getName()とCheckRangeValue()については、細かく指摘するとキリがなさそうなので、完全に書き直しました。元のコードと比較して何が違うのか、ご自分で確かめて下さい。

Answer 2

rangeのglobalエラー

については、「VBAのOrは短絡評価をしない」という仕様によるものです。

短絡評価というのは

「参照範囲がエラー」 Or 「参照先の値がエラー」

と書いたときに「参照範囲がエラー」だったら、「参照先の値がエラー」かどうかは評価しない（左が決まれば全体の結果は決まるため）、というものです。

VBAは常に両側を評価するため、「参照範囲がエラー」なのに参照先を確認しようとしてRangeを取得できない、ということになります。

対策として単純なのはIfを分けることです。

'なんらかの参照エラーがあるかどうかのフラグ
Dim hasMissingRef As Boolean
hasMissingRef = (InStr(nm.Value, "#REF") > 0)
'2個目以降の条件はIf文で分岐
If Not hasMissingRef Then hasMissingRef = (getName(nm.Name) = 1)

If hasMissingRef Then
   nm.Delete
   i = i + 1
End If

Select Caseを使うと短絡評価できるため、私は以下のように書くこともあります（Select Caseの使い方としてはやや邪道ですが）。

Select Case True
    Case InStr(nm.Value, "#REF") > 0, _
         getName(nm.Name) = 1
        nm.Delete
        i = i + 1
End Select

---

その他気になった点

• DisplayNamesの対象が明確ではないため、対象のブックを指定できるようにしましょう
• DisplayNamesは結果を返さないため、Functionの意味がありません
• 各種Functionは返り値の型を明示しましょう
• OK・NGの2択の判定であればBoolean型を使うのが適当です
• CheckRangeValueはいろいろ問題外

またgetName＆CheckRangeValueで判定したいものがが「参照エラーとなっているセル」の場合、natoさんのコードでも漏れる可能性があります。

これらを踏まえて私なりに書き直すと以下のような感じでしょうか。

Public Sub DeleteNames()
    Dim targetBook As Excel.Workbook
    Set targetBook = ActiveWorkbook
    
    '非表示となっている名前の定義を表示
    DisplayNames targetBook
    
    Dim delCnt As Long 'i から名前を変更
    
    '名前の定義の「値」、「参照範囲」に特定の文字列を含んでいた場合、該当する名前の定義を削除
    Dim nm As Name
    For Each nm In targetBook.Names
        
        '`[Name].RefersToRange`で参照先のセル範囲は取得できる
        Select Case True
            Case InStr(nm.Value, "#REF") > 0, _
                 ContainsErrValue(nm.RefersToRange, xlErrRef)
                
                nm.Delete
                delCnt = delCnt + 1
                
        End Select
        
    Next nm
    
    Call VBA.MsgBox(delCnt & "個の名前を削除しました")
    
End Sub

'非表示の名前の定義を再表示する
Public Sub DisplayNames(tgtBook As Excel.Workbook)
    Dim nm As Excel.Name
    For Each nm In tgtBook.Names
        If Not nm.Visible Then
            nm.Visible = True
        End If
    Next nm
End Sub

'セルの値にエラーが含まれているかどうか
'chkRng：対象のセル範囲
'errType：エラーの種類（Excel.XlCVErrorはセルのエラーの種類）
Public Function ContainsErrValue(chkRng As Excel.Range, errType As Excel.XlCVError) As Boolean
    Dim r As Excel.Range
    For Each r In chkRng
        
        If VBA.IsError(r.Value()) Then
            
            'errTypeのエラーかどうか
            'エラー値同士の比較じゃないと`型が一致しません。`となる（エラー値の仕様）
            If r.Value() = CVErr(errType) Then
                Let ContainsErrValue = True
                Exit Function
            End If
            
        End If
        
    Next r
End Function