エクセルVBA100本ノック。１8本目：名前定義の削除

この記事から得るもの

名前の管理をVBAを使って処理する方法が分かる。
参照先が不明（＃REF！）の名前を削除する。

１　今回のお題

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

f:id:bimori466:20201112055159p:plain

excel-ubara.com

２　今回のお題の意図

不要な（参照先がない）名前定義を削除する。

３　回答

今回も完全な勉強回です。ＶＢＡで名前の管理をいじることなど思ったこともありませんでした(;^ω^)。
お題の画像にあるような、「＃REF！」とは、「参照先がありません」ということを示しています。
では、どういった時にこの現象が起こるのか。
１　Sheetの削除
２　該当セルの削除

こんなところですね。
名前を使って、参照先を指定するという方法はあまり使わないので痛いところを付かれたお題となりました。

しかし、意外に簡単な処理です。実際にコードを見ていきましょう。

私の模範解答をカンニングしながら、作成したコード

Sub ノック18本目_1()
Dim HiddenNamesCount, DeleteNameCount As Integer
HiddenNamesCount = 0: DeleteNameCount = 0

For Each nm In ActiveWorkbook.Names
    If nm.Visible = False Then: nm.Visible = True: HiddenNamesCount = HiddenNamesCount + 1
    
    '参照範囲に「#REF!」が含まれる場合は削除
    If InStr(nm.RefersTo, "#REF") > 0 Then
        Debug.Print nm.Name & ":" & nm.RefersTo
        nm.Delete: DeleteNameCount = DeleteNameCount + 1
    End If
Next

MsgBox "非表示件数＝" & HiddenNamesCount & "件" & vbCrLf & _
        "　削除件数＝" & DeleteNameCount & "件"

End Sub

疑問（Q）、回答（A）方式で説明します。

Q１どうやって名前の管理の中身を処理するのか

A 　For Each nm In ActiveWorkbook.Names

補足

Namesを使うことで、名前の管理のデータにアクセスできます。
例えば名前の管理の、
「名前」は、nm.Name
「値」は、nm.Value
「参照範囲」は、nm.RefersTo
となります。

Q２参照先がない名前をどうやって判断するか。

A　 If InStr(nm.RefersTo, "#REF") > 0 Then

補足

InStr関数は、対象文字列が何文字目に含まれているかを調べる関数です。対象の文字が含まれていない場合「０」を返します。
つまり、参照先が切れている「#REF」がnm.RefersToに含まれていれば0以上（＞０）の条件に当てはまるのです。

Q３不要な名前をどうやって削除するのか。

A　nm.Delete

補足

今回お題の中に「参照範囲に「#REF!」が含まれる場合は削除しイミディエイトに「名前」と「参照範囲」を出力」とあるので、削除前に「Debug.Print nm.Name & ":" & nm.RefersTo」を記述します。削除後、削除件数のカウンタ（DeleteNameCount ）を＋１します。

注：名前が非表示のものがある

私も初めて知ったのですが、名前の管理の名前は非表示にできるんですね（nm.Visible = False）。何目的？？と疑問が残ります(;^ω^)。お題の中に非表示になっている名前をカウントしなさいというのがあるので、チェックしてカウントしますが、単純に削除するだけならVisible = Falseでも問題はありません。「For Each nm In ActiveWorkbook.Names」は非表示に関わらず、存在する名前をすべて処理してくれます。

４　一言

名前の管理はあまり使わないのですが、詳しい人なら多用している可能性ありますね。
そして、Sheetを削除して管理できなくなる(;^ω^)。
そうした不要な名前を一気に削除するのに便利なコードでした。いつか使う日がきそうです。

ではでは、この辺で(^^)/~~~

この記事から得るもの

１ 今回のお題

２ 今回のお題の意図

３ 回答