ブック内の名前定義を検出するExcel VBAマクロ (不要と思われるセル参照の検出): (旧) hinden (まほまほファミリー)

<< 途中下車。有楽町を軽く散策。(仕事を辞めて、帰路。道草。) | TOP | ブック内の名前定義を削除するExcel VBAマクロ (不要と思われるセル参照の削除) >>

2017年05月23日

ブック内の名前定義を検出するExcel VBAマクロ (不要と思われるセル参照の検出)

要りもせん (どこででも何にでも使ってやせん) 名前定義をそのままにしておくと、なにかとウザいことがございます。消しましょう。あっ、その前に、一覧を取りましょう。
　
名前定義。これですな、ずうっと気付かれずにいること、多いんですわ。処理自体に影響がない場合が大体ですので。で、要らんもんをくっつけたままコピーされたりメールされたり転用されたりして、増殖してゆくんですよ。これ、ウィルスじゃあないんですけど、「感染している」て表現していいと思います。

(ex. 関わったことのある職場 (複数件) では、長年の「やりっぱなし」が積もりに積もり重なって、おびただしい感染量に。もちろんその感染、自社内にとどまらず、ですわ。そしてその現状が、誰にも認識されておらん。セル参照先を見てみると、意図して定義してあるとは思えぬものばかり。それ以前に大体は参照エラーになっています。)

[放っておくとどんな害がある ?]

放置でも処理に実害はないです。あるとすれば…

・シートをコピーして増やそうと思った時に繰り返し「名前を使用しますか?」的なことを聞かれて「はい」で答えまくらなければならぬハメになったり。
・保存時に「有効な参照範囲と競合する名前がある」旨のウォーニングが表示されたり。
・しかし何より、セキュリティ (情報漏洩) の観点から、見過ごせぬ問題です。セル参照先が見せてはならぬトコになっちゃってるかもしれませんからね。

[注意事項]
なにもマクロ使わなくても一覧できるのでは、と思うカタもおられよう。しかし注意。
　
「数式」タブ⇒「名前の管理」画面に表示されるのが全てとは限りません。
VBAで検索すると「Visible:=False」指定のものが別途検出される、という場合がございます。
　
(※ マクロで名前を付ける際に
　
ActiveSheet.Names.Add Name:="名前A", RefersTo:="=$A$1", Visible:=False
　
のように「Visible」を「False」指定しておきますと、「名前の管理」画面には一覧されません。)

[というわけで、実際の、マクロ (現物)。]
(以下を貼ってご使用ください。)
　
　↓↓↓↓↓
　
' ブック内で定義されているセル範囲の名前定義をすべて取得し､Userform1のListBox1に表示します｡
' また､リストからセル範囲を選択するとLabel1にその参照範囲と表示・非表示の別とを表示します｡
' さらに､当マクロと同一のフォルダ内に「log.txt」の名でログを出力します。
Sub aaaName_Search()

Dim myName As Name
Dim msgTxt As String
If ActiveWorkbook.Names.Count = 0 Then

msgTxt = ActiveWorkbook.Name & " に名前は設定されていません。"
MsgBox msgTxt

Else

msgTxt = ActiveWorkbook.Name & " に名前は " & ActiveWorkbook.Names.Count & "個設定されています。"
UserForm1.Label2 = msgTxt
For Each myName In ActiveWorkbook.Names

'RefersTo は RefersToR1C1 でも Value でもいいらしい。
msgTxt = msgTxt & vbCrLf & vbCrLf & _
"Name : " & myName.Name & vbCrLf & _
"Visible : " & myName.Visible & vbCrLf & _
"RefersTo : " & myName.RefersTo & vbCrLf & _
"Parent : " & myName.Parent.Name
UserForm1.ListBox1.AddItem myName.Name

Next
UserForm1.Caption = "名前定義の一覧"
UserForm1.ListBox1.ListIndex = 0 '1行目を選択
UserForm1.Show
'MsgBox msgTxt

End If
'Debug.Print "********" & vbCrLf
'Debug.Print msgTxt
Call WriteLog(msgTxt)

End Sub
'
'ログを残す。
Sub WriteLog(str As String)

Open ThisWorkbook.Path & "\log.txt" For Append As #1
Print #1, str & vbCrLf
Close #1

End Sub
'
'以上は標準モジュールに、
'以下は UserForm1 に書き込んでください。
'
Option Explicit
'ブック内の名前定義を検出から呼ばれた場合、
'リストからセル範囲を選択するとLabel1にその参照範囲と表示・非表示の別とを表示します
Private Sub ListBox1_Change()

If UserForm1.Caption = "名前定義の一覧" Then

UserForm1.Label1.Caption = Mid(ActiveWorkbook.Names(UserForm1.ListBox1.Value).RefersToLocal, 2) _
& vbLf & "Visible = " & ActiveWorkbook.Names(UserForm1.ListBox1.Value).Visible _
& " ; " & "Parent = " & ActiveWorkbook.Names(UserForm1.ListBox1.Value).Parent.Name

End If

End Sub

[:次回は、いよいよ名前定義の削除。]
　↓　
[ブック内の名前定義を削除するExcel VBAマクロ (不要と思われるセル参照の削除)]

[関連する日記。]
　↓　
[テーマ「コンピュータ」のブログ記事一覧]

[解説・使用例など。]
　↓　
「Excel VBA] 99. 「VBA実例集エクセル」 (更新・追加、随時ばんばん入っています)。