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実例集エクセル」 (更新・追加、随時ばんばん入っています)。


ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本
秀和システム
立山 秀利

amazon.co.jpで買う
Amazonアソシエイト by ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本 の詳しい情報を見る / ウェブリブログ商品ポータル


hinden Takahashi, Hideki ひんでん 高橋 秀樹 | facebookひんでん (ユリコ暴走)(@Gemini_hinden)さん | Twitter


hinden Takahashi, Hideki / ひんでん 高橋 秀樹--------
about me
--------
hinden Takahashi, Hideki
ひんでん 高橋 秀樹
http://hinden.at.webry.info/200002/article_1.html

[events]
イベント情報一覧
http://www2u.biglobe.ne.jp/~hinden/live/
posted by hinden (まほまほファミリー) at 19:57| 東京 ☀| Comment(0) | TrackBack(0) | コンピュータ | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
この記事へのトラックバックURL
http://blog.seesaa.jp/tb/450160676
※言及リンクのないトラックバックは受信されません。

この記事へのトラックバック