任意のセル選択範囲内のセルの名前のコレクション

質問

任意に選択したセル範囲内のセルの名前のコレクションを取得したいと考えています。
On Error Resume Next
For Each 名前 In Selection
　　　処理内容
Next
On Error GoTo 0
と、すると取得することは可能なんですが、全てのセルを検索するため範囲が大きいと時間が掛かります。
Namesコレクションを利用して、セルアドレスがセル範囲に入っているか？を調べる方法も考えたのですが、これまた時間が掛かります。
どなたか？詳しい方！こんな方法もあるよ！！っての教えて頂けないでしょうか？宜しくお願い致します。

回答No.2

Wendy02 · ベストアンサー

vba_minaraiさん、こんばんは。

ご質問の最終的な目的が良く見えないので、テクニックのみの話に終始してしまいます。

ただ、実際のコーディングでは、Names(名前定義）をVBAで使う方法は避けたほうがよいです。オブジェクトブラウザで調べてみれば分りますが、曖昧な要素をもっているので、いわゆる「明示的」というVBAの前提からは外れてしまいます。

名前定義は、ある意味ワークシートの定数で、VBAでは、Formula として文字列扱いになっています。しかし、本来、Formulaは、Rangeオブジェクトなどの引数にはふさわしくありません。On Error トラップでも良いようですが、やはり見栄えが良くありません。

具体的な内容がわかりませんので納得させられないような気がしますが、通常、VBAでは、名前定義ではなく、Public Const にして、アドレスのリストを作って、配列変数にしてから、Unionでつなぐか、ローカル変数にして、個々のアドレスをUnionでつなぐかで設定します。

以下のように、Rangeオブジェクトの中に、数式文字列を入れる方法はありますが、私は、通常、このような曖昧なコードは書きません。

'-------------------------------------------
Sub NamesTesting()
Dim myName As Name
Dim ShName As String
ShName = ActiveSheet.Name
If TypeName(Selection) <> "Range" Then Exit Sub
For Each myName In Names
　If myName.RefersTo Like "=" & ShName & "!*" Then
　　If Not Intersect(Range(myName.RefersToLocal), Selection) Is Nothing Then
　　　 Range(myName.RefersToLocal).Interior.ColorIndex = 3
　　End If
　End If
Next
End Sub

'-------------------------------------------

補足

いつもいつもいつも適切なご指導有難う御座います。
＞Names(名前定義）をVBAで使う方法は避けたほうがよいです。オブジェクトブラウザで調べてみれば分りますが、曖昧な要素をもっているので、いわゆる「明示的」というVBAの前提からは外れてしまいます。
このように、何度も名前についてのご指導は、痛感しております。しかし、ご指導頂いて置きながら、名前に固執しているには理由があります。
本来は、マクロを組む人間が完結したものを作る必要があると思いますが、今やろうとしていることは、エクセルに慣れていない人も名前さえ定義すれば、フレキシブルに拡張できるようなものが出来ないか？と試行錯誤しております。
カード型データ的なものを考えていますので、アクセスとの連携も考えています。
いつも、抽象的な質問にもかかわらす丁寧なご指導有難うございます。

回答No.3

taocat

こんばんは。
こんな方法もあるよ・・・(^^;;;

＞任意に選択したセル範囲内のセルの名前のコレクションを取得したい

ということは、
「名前定義されたセル（範囲）」に、
「任意に選択したセル（範囲）」が
少しでも掛かっていたらその名前を全て取得するんですね？

例えば、

Sheet１
　　　名前Ｐ：　B2:B7
　　　名前Ｑ：　D5:F10
　　　名前Ｒ：　H1:J6
Sheet２
　　　名前Ｓ：　A1:A5
　　　名前Ｔ：　C1:C5
　　　名前Ｕ：　E1:E5

として、選択した範囲が、「Sheet１のA4:E12」であれば、
「名前Ｐ」と「名前Ｑ」を取得したい・・ですね？

-----------------------------------------------

Sub test()

　Dim myName
　Dim myRange As Range
　Dim myInSect As Range

　On Error Resume Next

　Set myRange = Selection

　For Each myName In ActiveWorkbook.Names

　　　Set myInSect = Application.Intersect(myName.RefersToRange, myRange)

　　　If Not myInSect Is Nothing Then
　　　　　　MsgBox myName.Name
　　　End If

　Next myName

End Sub

---------------------------------------------

何故エラートラップしてあるかは、それを外して確認して下さい。
また、必ずヘルプでRefersToRangeプロパティを覗いてください。
以上です。

補足

お礼が遅くなりまことに申し訳ありません。
ご指導頂いた内容は、何度も何度もよく読み理解していきたいと思います。本当に本当に有難う御座いました。今後とも宜しくお願い致します。

回答No.1

imogasi

>全てのセルを検索するため範・・
本当ですか。信じられない。私もよく判りませんが。
Dim cl As Range
For Each 名前 In Selection
Msgbox cl.Value
Next
の場合は、全てのセルを検索すると思いますが。
-------
B4:C7にNameAと名前定義して
シートのイベントプロシージュアーに
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set a = Range("nameA")
Set b = Intersect(Target, a)
If b Is Nothing Then
MsgBox "共通のセルでない"
End If
End Sub
として、実行してみると、うまく反応するようです。
しかしこれがいろんなケースで、質問者のなさっている方法より
「処理が早い」かどうかは判りません。
ただ高等（？？）なメソッド（Ｉｎｔｅｒｓｅｃｔ）を使うので、コーディング行は少なくなるように思う。
飛び離れた２つの範囲のどちらかに入っているか、は
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set a = Range("nameA")
Set b = Range("nameB")
Set c = Union(a, b)
Set d = Intersect(Target, c)
If d Is Nothing Then
MsgBox "共通のセルでない"
End If
End Sub
でできました。
回答に「自信あり」とは上記コードは少数例でテスト済みという意味ですのであしからず。

補足

早速のご指導有難う御座います。
いつも、いつも有難う御座います。
>全てのセルを検索するため範・・
の点ですが、選択範囲の全てのセルをに訂正させてください。
>高等（？？）なメソッド（Ｉｎｔｅｒｓｅｃｔ）？？？私は、まだ、見たこともないメゾッドです。
メゾッド自体に、程度の高低があるのかどうかは、解りませんが、そのメゾッドが使えるかどうかは、使う側の技量が高度であるどうかは、絶対にあると思います。
仰るとおり、コーディング行は少なくなる＝実行速度が速い可能性がかなり高いと言える場合が多い(単にループを回すとかの場合は明らかに該当しないと思いますがあ・・？)と思いますので、ご指導頂いた内容を、自分の血と肉となるようじっくり考えて習得したいと思います。
本当に、本当に有難う御座います。今後とも宜しくお願いいたします。