エクセルVBA セル選択範囲を調べるメソッドについ

質問

こんにちは
今回エクセルでセル選択範囲を調べる方法について探していたのですが”Selection.Address”が
見つかりました。
これは、結果を文字型で返す仕様となっていて、文字型では、応用処理がややこしそうになります。
離れた範囲で選択した場合"例　R1C1:R2:C2,R4C4:R5C5"　のように返ってきます"
そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
または、直接　希望する型で返ってくればそれでよいのですが、見つけることが出来ませんでした。

なお、エクセルのバージョンは２００３です。
もしなければ作成する予定です。

回答No.5

cj_mover

こんにちは。＃１～４、ｃｊです。

フラグ配列を使った例をあげることにしました。
応用できる機会が比較的多いと思います。
また、この方法だと、元々の行位置の順に格納できますから、
ソートする必要がありません。
また、重複の処理も簡単です。
一応、これは、元々の質問に対する答えに近いようにも思います。

Range オブジェクトのプロパティからフラグ配列を作って、
テーブル全体の値を格納した配列から
フラグ配列のインデックスに対応したレコードを
バッファに流し込みます。

（レコードの先頭が１行目から始まらない場合を考慮して書いていますので、その分少し長いです。）
（一般的なエラー処理も加えました。）

' ' ============================================================

Sub Re7761791c5()
　　Dim buf()　　　　　　　　　 ' 抽出レコードを流し込む配列
　　Dim mtxV()　　　　　　　　　' テーブル全体の値を格納する配列
　　Dim Target　　 As Range　　 ' Selectionを元に、テーブルからはみ出た範囲を削いだセル範囲
　　Dim a　　　　　As Range　　 ' Area ループ用
　　Dim nRowOffset　As Long　　 ' 『テーブル上の行番地』と『配列インデックス』との相対補正
　　Dim nYSize　　　As Long　　 ' テーブルの行サイズ
　　Dim nXSize　　　As Long　　 ' テーブルの列サイズ
　　Dim nTop　　　　As Long　　 ' Area 先頭行の相対番地
　　Dim nBottom　　 As Long　　 ' Area 最下行の相対番地
　　Dim arFlg()　As Boolean　　 ' 抽出レコードをマークするフラグ配列
　　Dim cnt　　　　 As Long　　 ' 抽出レコード数カウンタ
　　Dim i As Long, j As Long　　' ループ用カウンタ

'　　Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー

　　' ' セル範囲以外を選択中なら、抜ける
　　If TypeName(Selection) <> "Range" Then Exit Sub

　　With Sheets("リスト")
　　　　nRowOffset = 0　　' 先頭.Row - 1
　　　　' ' 最下行位置を取得して、行サイズを求める
　　　　nYSize = .Cells(Rows.Count, "A").End(xlUp).Row - nRowOffset
　　　　nXSize = 11　　' 列サイズ

　　　　' ' テーブル全体を捉える
　　　　With .Cells.Resize(nYSize, nXSize).Offset(nRowOffset)
　　　　　　' ' Selection について、テーブルから、はみ出た部分を削いでおく
　　　　　　Set Target = Application.Intersect(.Cells, Selection)
　　　　　　' ' Selection がテーブル内にない場合、抜ける
　　　　　　If Target Is Nothing Then Exit Sub
　　　　　　' ' テーブル全体の値を配列として格納しておく
　　　　　　mtxV() = .Value
　　　　End With
　　End With

　　' ' フラグ配列を行サイズで定義
　　ReDim arFlg(1 To nYSize) As Boolean
　　' ' Target 各Areaをループ
　　For Each a In Target.Areas
　　　　' ' Area の先頭行位置を相対位置で求める
　　　　nTop = a.Row - nRowOffset
　　　　' ' Area の行数を取得して、最下行位置を相対位置で求める
　　　　nBottom = nTop + a.Rows.Count - 1
　　　　' ' Area の先頭行から最下行に対応したフラグをTrueに設定する
　　　　For i = nTop To nBottom
　　　　　　' ' （他のAreaと重複している場合）既にTrueに設定してある場合は何もしない
　　　　　　If Not arFlg(i) Then
　　　　　　　　arFlg(i) = True
　　　　　　　　' ' 抽出するレコード数をカウント
　　　　　　　　cnt = cnt + 1
　　　　　　End If
　　　　Next i
　　Next a

　　' ' 抽出レコードを流し込む配列をレコード数と列サイズで定義
　　ReDim buf(1 To cnt, 1 To nXSize)
　　cnt = 0
　　' ' フラグ配列を総なめして、Trueならbufに流し込む
　　For i = 1 To nYSize
　　　　If arFlg(i) Then
　　　　　　cnt = cnt + 1
　　　　　　For j = 1 To nXSize
　　　　　　　　buf(cnt, j) = mtxV(i, j)
　　　　　　Next j
　　　　End If
　　Next i

' Range("M1").Resize(cnt, nXSize).Value = buf ' ダミー

　　' ' オブジェクト変数の解放
　　Set Target = Nothing
　　' ' 配列変数の初期化
'　　Erase mtxV, arFlg, buf
End Sub

' ' ============================================================

応用できる場面が見つかるといいのですけれど。

以上です。

回答No.4

cj_mover

こんにちは。＃１、２、３、ｃｊです。＃３補足欄へのレスです。

ご自分で研究しながら着実に進んでいることが、こちらにも伝わってきます。
非常にいい印象ですし、こちらもやりがいがあります。
さておき、
　複数のセルブロックを指定して、
　最終的に必要なレコードだけを、
　ひとつの２次元配列に、（できればSortして）格納したい
という【テーマ】に限って考えてみます。

まずは、＃３でご提示のコードについて。

Select して Selection を追いかけるのは何かと紛れの元になりますから、
Range型の変数に一旦格納してみます。
　Set Target = Application.Intersect(Sheets("リスト").Range("A:k"), Selection.EntireRow)
　If Target Is Nothing Then Exit Sub
' ↑(Dim Target As Range)
これ↑で、
（テーブルの外を選択していたり、他のシートで実行してたり）エラーを回避できる意味もありますし、
後に続く処理が Target に対するものであることが明示的になり、より確実なものになります。
オブジェクト型の変数はプロシージャの最後で
　Set Target = Nothing
のように解放してあげるようにしましょう。

　'配列サイズ設定
の
> ReDim buf(i, 11)
正しくは
　ReDim buf(1 To i, 1 To 11)
ですね。
配列変数を扱う時は、最小の添え字（LBound）が 1 なのか 0 なのか
（或いはそれ以外なのか）恒に、注意を払うようにしましょう。
ご提示のコードでは、buf(0, any)、buf(any, 0)がすべてEmpty値になっていて
不必要なことをしていることになります。

もう一点、.Intersectメソッドを使う利点として重複は既に除かれていることを利用すると、
　i = Application.Intersect(Sheets("リスト").Range("A:A"), Target).Count
　ReDim buf(1 To i, 1 To 11)
という纏め方も可能ではあります。
（簡単に書けるという意味です。処理が速くなる訳ではありません）
（同じ.Intersectメソッドを使うのにも、こちらは、
　エラーになる要因が既にクリアになっているので省略して書けます）

　'選択範囲を配列buf(行,列)へ設定
は、差し当たってダメ出しするほどの問題ではないのですけれど、
一貫性を通した書き方をすると、こんな感じでしょうか。
　i = 0
　For Each a In Target.Areas
　　For Each rw In a.Rows
　　　i = i + 1
　　　j = 0
　　　For Each r In rw.Cells
　　　　j = j + 1
　　　　buf(i, j) = r.Value
　　　Next r
　　Next rw
　Next a
' ↑(Dim r As Range)
> For Each rw In a.Rows <　これは＃３で私が書いたことの実践なのでしょうけれど、
レコード毎に切り分ける必要がないのならば .Rows プロパティを使う必要もなくて、
今回の【テーマ】では .Areas プロパティすら省略できて、うんと簡単に
　i = 1
　j = 1
　For Each r In Target
　　buf(i, j) = r.Value
　　If j = 11 Then
　　　j = 1
　　　i = i + 1
　　Else
　　　j = j + 1
　　End If
　Next r
' ↑(Dim r As Range)
↑こんな風に纏めることも可能です。

一旦、整理します。今回の【テーマ】では .Areas プロパティ無しでも出来るということで。

Sub Re7761791r3()
　　Dim buf()
　　Dim Target As Range, r As Range
　　Dim i As Long, j As Long

　　' Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー
　　Set Target = Application.Intersect(Sheets("リスト").Range("A:k"), Selection.EntireRow)
　　If Target Is Nothing Then Exit Sub

　　'配列サイズ設定
　　i = Application.Intersect(Sheets("リスト").Range("A:A"), Target).Count
　　ReDim buf(1 To i, 1 To 11)

　　'選択範囲を配列buf(行,列)へ設定
　　i = 1
　　j = 1
　　For Each r In Target
　　　　buf(i, j) = r.Value
　　　　If j = 11 Then
　　　　　　j = 1
　　　　　　i = i + 1
　　　　Else
　　　　　　j = j + 1
　　　　End If
　　Next r

　　Set Target = Nothing
End Sub

.Intersectメソッドを使う利点を最大限活かす手法にも触れておきます。
.Intersectメソッドで得られるTargetのすべて.Areasが、共通の列幅をもっているならば、
そのまま.Copyして貼りつけてしまうことで、
・重複は削除され・連結した配列として・Sortされた配列を
シート上に作成できます。

Sub Re7761791_40()
　　Dim buf()
　　Dim Target As Range

　　' Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー
　　Set Target = Application.Intersect(Sheets("リスト").Range("A:K"), Selection.EntireRow)
　　If Target Is Nothing Then Exit Sub

　　Target.Copy Destination:=Range("M2")

　　'選択範囲を配列bufへ格納
　　buf = Range("M1").CurrentRegion.Value

　　Set Target = Nothing
End Sub

作業セル（作業シート）を（一時的にでも）設けることが許されるならば、
上記のような手法も場合によって（目的によって）は有効です。
案外、配列変数を使わなくとも、目的が果たせるケースも少なからずあることでしょう。
（上例の "M2" は、上の行が空行、左の列が空列、以下空セル、という条件で適宜指定してください）

///
私、今、余裕がないので、このレスは一旦、ここまでにしてあらためて続きを書きます。
次に、今回の【テーマ】に即した形で、Sortも併せ、配列への格納方法を探ってみます。
外部オブジェクトは使わずに純粋にＶＢＡだけを用いるレベルで、
なるべく難しい関数などは使わない形で書いてみようと思います。
ある程度処理の速度を速める意図で、
配列変数を使うメリット（ＶＢＡ独特のメリット）を出せるように考えてみます。
ポイントはRangeオブジェクトへのアクセスを最小限にして、
メモリ上の配列変数での処理を優先させること、になります。
ただ、色々な方法があるので、どんなものを紹介したらいいか迷っています。
文字数制限に掛かってしまうので、どのみち、もう１レスすることにはなりますが、
ちょっと体力的な問題を抱えてたりもするので、少し、時間をくださいませ。
それでは、また。

回答No.3

cj_mover

こんにちは。＃１、２、ｃｊです。
＃２補足欄へのレスです。

> リスト形式のデーターテーブル（シート）がある。
> ユーザーは処理対象としたいレコード（セル）を任意に選択する（複数可）。
> プログラムは、選択されたレコード（セル）を対象に処理（他のシートにコピーや印刷
> など。差込印刷）をする。

> これらのことをするのにプログラムはユーザーの選択セル情報を知る必要があるのでそ
> の方法を探していたところでした。

そういう目的ならば、行番地の数値を追いかけずとも、
Rangeオブジェクトで取得した方が何かと便利です。
次の例では、例えば、ユーザー操作で重複して選択されたセル範囲があった場合でも
自動的に重複を除いてくれます。
例えばコピーなら、そのままコピペ（VBAでも手作業でも）すれば
有用なレコードだけを整列して貼り付けできますし、、、。
割とお得な近道だと思いますが、どうでしょうか。

Sub Re7761791ja()
　　Range("B2,D4,B4:B5,B7:B9,B8,B11:B14").Select ' ダミー
　　MsgBox "Prep."
　　Application.Intersect(ActiveCell.CurrentRegion.EntireColumn, Selection.EntireRow).Select
　　MsgBox "Got" & vbLf & Selection.Address(0, 0)
End Sub

もしレコードごとのデータをレコードごとに切り分けて処理する必要がある場合は
　　For Each a In Selection.Areas
　　　　For Each rw In a.Rows
のようにも切り分け可能ですし、
配列に格納したデータを行番地を参照して取得するような方法にも応用できます。
また、レコードそのものではなく、キーとなるIDなどをポイントする目的でも
基本的には同じように処理するのが簡単です。

> いずれにしろ構想の段階ですが、文字型では、処理が難しくなるのではないのかなぁと
> 思い、数値型でのアドレス情報での処理をしようと思った次第です。

確かに、クエリやコマンドや正規表現が苦手という方は沢山いらっしゃいますから、
苦手意識がある人にはrange.Address でさえも難しいのかも知れませんね。
一般論としては文字列で処理するよりは数値で処理する方が優る、という場面が多いのも事実で
仰ることはよく解ります。
ただ、数値配列の取得も、数値配列を参照してRange オブジェクトを取得する方法も
Excelには用意されてませんので、その代わりになる機能を他に求めるのが普通のやり方だと思います。
そういう意味では＃１で紹介した手法もどこかで役に立つことがあればいいですけどね。

注）.Select、Selectionは概念提示の為便宜的に使っているもので実践には使いません。

一応。質問者さんがここでメソッドと呼んでいるのはプロパティですので確認してみてください。

とりあえず、以上です。

補足

cj_moverさん　こんにちは
たびたびありがとうございます。

ご回答をいただいてからそれをヒントに何とか自分で解決しようとして、さらに調べたり
テストプログラムを走らせたりしてたら報告が遅くなってしまいました。
出来上がったコードが以下の通りです。

Dim buf(), a, rw
Dim i As Integer, j As Integer

Application.Intersect(Sheets("リスト").Range("A:k"), _
Selection.EntireRow).Select

'配列サイズ設定
i = 0
For Each a In Selection.Areas
i = i + a.Rows.Count
Next
ReDim buf(i, 11)

'選択範囲を配列buf(行,列)へ設定
i = 0
For Each a In Selection.Areas
For Each rw In a.Rows
i = i + 1
For j = 1 To 11
buf(i, j) = Cells(rw.Row, j)
Next
Next
Next

これの結果は、配列buf(行 ,列)にセルのデータが代入されます。

選択範囲が何個もあるときなど、配列の行側の順番が上から順にならないことがあります。
この辺は、改善したかったのですが、難しくなりそうなので諦めました。
この配列データを元に自分がしたい処理が出来ると思います。

ただ、このコードは、いまのスキルで書いた為、多少無駄があるのかもしれません。
関数等を利用して数行で出来る方法があれば、ヒントを頂けたら有り難く思います。
（実は、これを探していて時間がかかりました。　元々ないから見つからなかったのかも
　しれません。）

回答No.2

cj_mover

なんか、ちょっとわかった気がしたので重ねてレスしてみます。

　　Range("A1:C5,D6:E10,G3:G12,B15:E16")
を例えにすると
　　matrix = [{1,1,5,3;6,4,10,5;3,7,12,7;15,2,16,5}]
で、得られるような
　　{top,left,bottom,right}
矩形範囲毎に絶対座標を配列にしたものを
さらに二次元配列や二段階配列にする、みたいなことでしょうかね？

そういうことでしたら
> そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
> してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
残念ながら用意されていないです。なので、ご自分で工夫するしかないですね。

少し気になるのは最終的にそれらの座標を元に何をしたいか、ということです。
そこまでの見通しがあれば、また違うアドバイスもできるかも知れませんけれど。

"...でなければ出来ない"事例、というのは
私の経験では、相当に特殊で、例えば、
Excel2003までは 8192Areas より多い範囲を返してくれない.SpecialCells メソッドを拡張して自作した時
ぐらいで、通常は
　.Address プロパティ、.Row プロパティ .Column プロパティ、
　Application.ConvertFormula メソッド、などを組み合わせ、
文字列処理して、セル範囲の参照文字列で工夫した方が
まだ、扱い易いように思います。
（私の想像力が乏しいだけで、必然的なニーズがあるのかも知れませんが。）

それから、
> もしなければ作成する予定です。
ということなので、一応、説明しておきますが、
Excel2003 で選択できるセルブロック（=矩形範囲、Area）の数は8192 までです。
ところが、.Address プロパティは 255文字までしか返しません。
255文字を超えた部分についてはバッサリ無視して、
不完全な結果だということを知ることさえ出来ません。
この点を理解した上で取り組まれた方がよいかと思います。
確実にやるなら、
.Address を採るにしても、Areaごとにするとか、
あらかじめ、Areas数を限定しておくとか、
対策が必要です。
なので、Areasごとに、.Row .Column を採っていった方が
（＃１下から２番目の例）結局能率的な場合もあると思います。

あとは、二次元配列なのか、ユーザー定義型（Type）を配列にするか、とか、
何にせよ、目的次第ですけれども。

とりあえず、以上です。

補足

cj_moverさん　こんにちは
アドバイスありがとうございます。
御回答の通りで、このような配列で返ってくることを求めていました。
次のような場合に使用出来ることを考えています。
エクセルでよく利用する使い方のではと自分自身で勝手に思っているのですが。

リスト形式のデーターテーブル（シート）がある。
ユーザーは処理対象としたいレコード（セル）を任意に選択する（複数可）。
プログラムは、選択されたレコード（セル）を対象に処理（他のシートにコピーや印刷
など。差込印刷）をする。

これらのことをするのにプログラムはユーザーの選択セル情報を知る必要があるのでそ
の方法を探していたところでした。
いずれにしろ構想の段階ですが、文字型では、処理が難しくなるのではないのかなぁと
思い、数値型でのアドレス情報での処理をしようと思った次第です。

あと、.Areasは今まで知らなかったメゾッドですが、この処理に利用できるのではない
のかなぁと感じました。
データ数の制限は、元データがそんなに大きくないのでとりあえずは大丈夫です。

回答No.1

cj_mover

こんにちは。

> そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
> してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
> または、直接　希望する型で返ってくればそれでよいのですが、見つけることが出来ませんでした。
ご希望、に対する私の理解が不十分なようなので、思い当たる限りの解釈で、
応用し易そうな手法だけ、列挙したコードを掲げます。
ＶＢＥで、Ｃｔｒｌ＋Ｇキーを押すとアクティブになる[イミディエイトウィンドウ]
に表示される結果と、それに対応したコードとを照らし合わせれば、
大体のことは理解してもらえるように書いたつもりです。

Sub Re7761791a()
　　Dim matrix, arr, v
　　Dim rArea As Range
　　Dim r As Range

　　Range("A1:C5,D6:E10,G3:G12,B15:E16").Select ' ←テスト用のダミーです
　　If TypeName(Selection) <> "Range" Then Exit Sub

　　Debug.? "◆いわゆる普通のＡ１形式で .Address()を取得" _
　　　　& vbLf & "　? Selection.Address(0, 0)" _
　　　　& vbLf & "　⇒　" & Selection.Address(0, 0) & vbLf

　　Debug.? "◆Ｒ１Ｃ１形式で .Address()を取得" _
　　　　& vbLf & "　? Selection.Address(ReferenceStyle:=xlR1C1)" _
　　　　& vbLf & "　⇒　" & Selection.Address(ReferenceStyle:=xlR1C1) & vbLf

　　Debug.? "◆各Area毎 .Address()をＡ１形式で取得"
　　For Each rArea In Selection.Areas
　　　　Debug.? , rArea.Address(0, 0)
　　Next rArea
　　Debug.?

　　Debug.? "◆.Address()を"",""区切りで配列変数に格納したものを出力"
　　arr = Split(Selection.Address(0, 0), ",")
　　For Each v In arr
　　　　Debug.? , v
　　Next v
　　Debug.?

　　Debug.? "◆各Area毎 左上の .Row .Column 右下の .Row .Column"
　　For Each rArea In Selection.Areas
　　　　With rArea
　　　　　　Debug.? .Row, .Column;
　　　　　　With .Cells(.Count)
　　　　　　　　Debug.? , .Row, .Column
　　　　　　End With
　　　　End With
　　Next rArea
　　Debug.?

　　Debug.? "◆各Areaの値を配列変数に格納して配列のサイズを確認"
　　For Each rArea In Selection.Areas
　　　　Debug.? , rArea.Address(0, 0)
　　　　matrix = rArea.Value
　　　　Debug.? , "1 to "; UBound(matrix, 1), "1 to "; UBound(matrix, 2)
　　Next rArea
　　Debug.?

End Sub

お礼

cj_moverさん　こんにちは
質問をしたものです。
早速ありがとうございます。
確認いたしますのですぐに返事が出来ませんが、一報としてお礼申し上げます。