任意に選択したセル範囲内のセルの名前のコレクションを取得したいと考えています。
On Error Resume Next
For Each 名前 In Selection
処理内容
Next
On Error GoTo 0
と、すると取得することは可能なんですが、全てのセルを検索するため範囲が大きいと時間が掛かります。
Namesコレクションを利用して、セルアドレスがセル範囲に入っているか?を調べる方法も考えたのですが、これまた時間が掛かります。
どなたか?詳しい方!こんな方法もあるよ!!っての教えて頂けないでしょうか?宜しくお願い致します。
No.2ベストアンサー
- 回答日時:
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
- 回答日時:
こんばんは。
こんな方法もあるよ・・・(^^;;;
>任意に選択したセル範囲内のセルの名前のコレクションを取得したい
ということは、
「名前定義されたセル(範囲)」に、
「任意に選択したセル(範囲)」が
少しでも掛かっていたらその名前を全て取得するんですね?
例えば、
Sheet1
名前P: B2:B7
名前Q: D5:F10
名前R: H1:J6
Sheet2
名前S: A1:A5
名前T: C1:C5
名前U: E1:E5
として、選択した範囲が、「Sheet1のA4:E12」であれば、
「名前P」と「名前Q」を取得したい・・ですね?
-----------------------------------------------
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
- 回答日時:
>全てのセルを検索するため範・・
本当ですか。信じられない。私もよく判りませんが。
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
として、実行してみると、うまく反応するようです。
しかしこれがいろんなケースで、質問者のなさっている方法より
「処理が早い」かどうかは判りません。
ただ高等(??)なメソッド(Intersect)を使うので、コーディング行は少なくなるように思う。
飛び離れた2つの範囲のどちらかに入っているか、は
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
でできました。
回答に「自信あり」とは上記コードは少数例でテスト済みという意味ですのであしからず。
この回答への補足
早速のご指導有難う御座います。
いつも、いつも有難う御座います。
>全てのセルを検索するため範・・
の点ですが、選択範囲の全てのセルをに訂正させてください。
>高等(??)なメソッド(Intersect)???私は、まだ、見たこともないメゾッドです。
メゾッド自体に、程度の高低があるのかどうかは、解りませんが、そのメゾッドが使えるかどうかは、使う側の技量が高度であるどうかは、絶対にあると思います。
仰るとおり、コーディング行は少なくなる=実行速度が速い可能性がかなり高いと言える場合が多い(単にループを回すとかの場合は明らかに該当しないと思いますがあ・・?)と思いますので、ご指導頂いた内容を、自分の血と肉となるようじっくり考えて習得したいと思います。
本当に、本当に有難う御座います。今後とも宜しくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- Excel(エクセル) マクロ セルの選択 5 2022/08/12 22:47
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Excel(エクセル) Excelのプルダウンメニューの内容を人によって可変する方法 2 2023/03/28 14:52
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Visual Basic(VBA) シート間で同じ値があったら指定範囲をコピーして貼り付け 1 2022/11/07 08:01
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- Excel(エクセル) 余計なお世話的な「入力規則」?対策は? 2 2023/01/14 12:39
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/02/10 17:07
- Excel(エクセル) VBA 特定の列に入っているテキストをコピペ 2 2023/06/14 11:24
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【エクセル】IF関数 Aまたは...
-
エクセルで指定したセルのどれ...
-
対象セル内(複数)が埋まった...
-
セルをクリック⇒そのセルに入力...
-
貼り付けで複数セルに貼り付けたい
-
エクセルのセルの枠を超えて文...
-
エクセル 足して割る
-
エクセル オートフィルタで絞...
-
Excelでのコメント表示位置
-
Excelで数式内の文字色を一部だ...
-
(Excel)数字記入セルの数値の後...
-
エクセルの一つのセルに複数の...
-
エクセルvba (ByVal Targ...
-
枠に収まらない文字を非表示に...
-
excelのCOUNTIF関数で、『範囲=...
-
Excel2003 の『コメント』の編...
-
excelの特定のセルの隣のセル指...
-
Excelの関数で、性別が男なら『...
-
excelで検索値の入っているセル...
-
エクセルで作成したカレンダー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで指定したセルのどれ...
-
【エクセル】IF関数 Aまたは...
-
対象セル内(複数)が埋まった...
-
エクセル 足して割る
-
Excelで数式内の文字色を一部だ...
-
貼り付けで複数セルに貼り付けたい
-
Excelでのコメント表示位置
-
セルをクリック⇒そのセルに入力...
-
EXCEL VBA セルに既に入...
-
excelのCOUNTIF関数で、『範囲=...
-
【Excel】 セルの色での判断は...
-
エクセル オートフィルタで絞...
-
エクセルのセルの枠を超えて文...
-
(Excel)数字記入セルの数値の後...
-
Excelで、「特定のセル」に入力...
-
エクセルの一つのセルに複数の...
-
複数のセルのいずれかに数字が...
-
excelの特定のセルの隣のセル指...
-
数式を残したまま、別のセルに...
-
ハイパーリンクの参照セルのズ...
おすすめ情報