ExcelVBA 非連続域の扱い(01)

質問

お世話になります。

添付映像の、ような
非連続域の、扱いに
困って、います

下記に、記載の
コードに、おいて

2回目、以降に
Function　最小値域(…
に、
制御が、回た
際の
>フィールド.Rows.Count
が、
１に、成り
困って、います。

と、言うか
抑も、

非連続域の、扱い方が
全く
解って、いません

どう、取得し、
どう、扱い、
どう、指定し、
どう、渡す、
のか…　等、

なので
>Evaluate("MIN("　&　フィ…
や、
>For　Each　カウンター　In　フィールド.Range(Cells(…
等の、
Range指定、等も
間違えて、いる
と、思います

其処で、
非連続域の、扱い
に、ついて

どうぞ、ご指南を
宜しく、お願いします。

　　　　　　　　　　　　　　　記
Option　Base　1
Option　Explicit

Type　ランゲポイント形式
　　左　As　Long
　　右　As　Long
　　上　As　Long
　　下　As　Long
End　Type

Function　最小値域(ByVal　フィールド　As　Range,　ByVal　列　As　Long,　Optional　ByVal　指標値　As　Variant)　As　Range
Dim　ポイント　As　ランゲポイント形式,　ランゲ　As　Range,　カウンター　As　Range,　注目行　As　Long
　Let　ポイント.上　=　1
　Let　ポイント.左　=　1
　Let　ポイント.下　=　フィールド.Rows.Count
　Let　ポイント.右　=　フィールド.Columns.Count
　Set　ランゲ　=　Nothing
　Set　カウンター　=　Nothing
　If　IsMissing(指標値)　_
　Then
　　Set　指標値　=　Evaluate("MIN("　&　フィールド.Range(Cells(列,　ポイント.上),　Cells(列,　ポイント.下)).Value　&　")")
　End　If
　For　Each　カウンター　In　フィールド.Range(Cells(列,　ポイント.上),　Cells(列,　ポイント.下))
　　If　カウンター.Value　=　指標値　_
　　Then
　　　Let　注目行　=　カウンター.Row
　　　If　ランゲ　Is　Nothing　_
　　　Then
　　　　Set　ランゲ　=　フィールド.Range(Cells(注目行,　ポイント.左),　Cells(注目行,　ポイント.右))
　　　Else
　　　　Set　ランゲ　=　Union(ランゲ,　フィールド.Range(Cells(注目行,　ポイント.左),　Cells(注目行,　ポイント.右)))
　　　End　If
　　End　If
　Next
　Set　最小値域　=　ランゲ
End　Function

Sub　main()
Dim　ダミー　As　Range
　Set　ダミー　=　最小値域(最小値域(最小値域(Range("sheet2!B2:e9"),　2,　"A"),　3),　4)
End　Sub
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　以上

回答No.2

real beatin（@realbeatin） · ベストアンサー

（No.1の続き、です）

複数領域のセル範囲を使いこなしたいなら、
　range.Areas プロパティ
をまず、概念的に理解を深めることが、とっても重要です。
　　rng(2, 3) のように普通の省略形で書いて成立する参照であっても実は
　　rng.Areas(1).Cells(2, 3) が正しいという場合が結構あって、、、
とか、、、これは、
　　領域の参照を省略した場合は、常に、
　　rng.Areas(1)
や
　　rng.Areas(1).Cells(1, 1)
　　を起点として、
　　rangeオブジェクトのメソッドやプロパティが機能する
ということを意味しています。
上記のコードに挙げている例では、
　　rngRet.Areas(1).Cells(1, 1).EntireColumn
は、
　　rngRet(1, 1).EntireColumn
に。
　　rngRet.Areas(1).Columns.Count
は、
　　rngRet.Columns.Count
に。
本来の意味（由来）を想像出来ないような形での省略が可能で、
また、暗黙の内に省略されている場面を目撃することの方が多い、のです。

> For　Each　カウンター　In　フィールド.Range(Cells(列,　ポイント.上),　Cells(列,　ポイント.下))
（行列の指定が反転しているのはともかく）
（[フィールド]の中身は実行中に変動するので成立していないことも置いといて）
この書き方では、先頭の矩形範囲.Areas(1)の左上セル.Cells(1, 1)を
起点とした参照にしかなっていません。
Areas プロパティを使ったFor Each ループの使用例を挙げておきましたので
ゆっくり考えてみて下さい。
（ただし、
　概念的な理解を援ける趣旨での記述であって
　実用コードを意識したものではありません）

複数領域のセル範囲を扱うなら、
　range.Areas プロパティ
　range.Rows プロパティ
　range.Columns プロパティ
　range.Intersect メソッド
　range.Union メソッド
等、コレクション風味のrangeオブジェクト(=Areas As Range)を返すもの
を一通りお浚いして、基本を押さえておきましょう。
例えば、上に挙げた（実用を意識していないのですが）
　　For Each rngArea In rngRet.Areas
　　　　For Each rngRowPart In rngArea.Rows
という記述についても、
　　　　For Each rngRowPart In rngRet.Rows
の１行で（対応するNext削除すれば）、同じ結果を返してくれます。
Rows プロパティも複数領域を普通に扱える、ということです。

複数領域の要素としての矩形範囲それぞれを、
上下左右のポイントで捉えようとするのは、
基本を踏まえた上での、次のステージでは、可能性があると思います。
私個人は、
　Cells(y, x).Resize(h, w)
のように、
起点の座標x,yと高さh幅wで表現することが殆どで、
　Range(Cells(top, left), Cells(bottom, right))
のような書式は、まったく使いません。
上下左右を扱うのは、
　Range("B3:E3,B5:E6,B9:E9")　' (上記マクロ実行時のイミディエイトウィンドウより)
のように、参照文字列で直接、複数領域を指定したい時の
各矩形範囲への参照を得たい時ぐらいです。
実際使うのは、
　Range("B:E (3:3,5:6,9:9)")
みたいな書式ですが、領域を扱う上ではこの書式こそ最高に重宝します。
　range.Resize プロパティ
これも使いこなせる方が、領域を合成する時なんかには有用です。

ご質問掲載４時間後の閲覧数が113とこの時期にしては特異でしたので、
閲覧者さんへの影響？も考えて、迷った末、
１度だけ回答することにしました。
態々お断りするのは、質問タイトルにある付番に反応してのものです。
教場ではないし、私的なＳＮＳでもないので、
継続的な"教授"を求めていることを暗示されるのは如何なものかと。
現実にお困りの場面で、
質問者と回答者という立場で関われるような質問であれば、
また、お手伝いできること、"教示"できるもあるかも知れませんが、
この課題につきましては、以上とさせてください。
ご健闘を。

お礼

有難うございます

〉複数領域のセル範囲を扱うなら、
〉　range.Areas プロパティ
〉　range.Intersect メソッド

OfficeTanaka
氏の
掲載を、初め

様々な、掲示を
拝見させて、頂いて
おりましたが

此等を
目に、するのは
初めての、事

お陰で
光明を、得た
と、思えます

重ねて、申します
有り難うございます

〉質問タイトルにある付番に反応しての

今まで
幾度と、なく
質問させて
頂く、中で

お礼欄、にて
詳細を
お伺い、する
余り

多段に、渡る
質疑応答を

お付き合い、
頂く、事も
多く、なって
おりました

しかし、
此れは、良くない
と、反省し

テーマ内で
別途、新たに
発起した、質問は

別で、聞くもの
と
改めました

此れを、受け
ご回答、頂く方
への、配慮
と、して

テーマの、連続性
詰まりは、
あぁ、あの続きか
と

容易に、想起
頂く、事が
一助に、なれば
と、

今回は
未来を、想定し
付けさせて、頂いた
振り番、です

他意は、ありません
ので
ご容赦、下さい

回答No.1

real beatin（@realbeatin）

' ' //
Function ExtractMinV_Areas2Areas(ByRef ExtractedTable As Range, _
　　　　　　　　　　ByVal nCol As Long, _
　　　　　　　　　　Optional ByVal vIndex As Variant) As Range
Dim c As Range
Dim rngInt As Range
Dim rngNewTable As Range
　　Set rngInt = Intersect(ExtractedTable, ExtractedTable(1, nCol).EntireColumn)
　　If IsMissing(vIndex) Then vIndex = WorksheetFunction.Min(rngInt)
Debug.? "■'基のTable'= Range("""; ExtractedTable.Address(0, 0); """)"
Debug.? , "'列相対'="; nCol, "'検索値/最小値'= "; vIndex
Debug.? , "'検索範囲'= "; rngInt.Address(0, 0)
Debug.? "'Table↓抽出↓集積中'"
　　For Each c In rngInt
　　　　If c = vIndex Then
　　　　　　If rngNewTable Is Nothing Then
　　　　　　　　Set rngNewTable = Intersect(ExtractedTable, c.EntireRow)
　　　　　　Else
　　　　　　　　Set rngNewTable = Union(rngNewTable, Intersect(ExtractedTable, c.EntireRow))
　　　　　　End If
Debug.? "　　　↓", rngNewTable.Address(0, 0)
　　　　End If
　　Next
Debug.? "'抽出後のTable'= Range("""; rngNewTable.Address(0, 0); """)", "■"
　　Set ExtractMinV_Areas2Areas = rngNewTable
End Function
' ' //
Sub Re9325848w()
Dim rngRet As Range
　　Set rngRet = ExtractMinV_Areas2Areas( _
　　　　　　　　　　ExtractMinV_Areas2Areas( _
　　　　　　　　　　　　ExtractMinV_Areas2Areas( _
　　　　　　　　　　　　　　Sheets("Sheet2").Range("B3:E9") _
　　　　　　　　　　　　, 2, "A") _
　　　　　　　　　　, 3) _
　　　　　　　　, 4)
　　rngRet.Select

' ' 以下、回答用オプション
Dim vMatrix
Dim rngArea As Range
Dim rngRowPart As Range
Dim rngCell As Range
Dim nY As Long, nX As Long
　　ReDim vMatrix(1 To Intersect(rngRet, rngRet(1, 1).EntireColumn).Count, 1 To rngRet.Areas(1).Columns.Count)
　　For Each rngArea In rngRet.Areas
　　　　For Each rngRowPart In rngArea.Rows
　　　　　　nY = nY + 1
　　　　　　nX = 0
　　　　　　For Each rngCell In rngRowPart.Cells
　　　　　　　　nX = nX + 1
　　　　　　　　vMatrix(nY, nX) = rngCell
　　　　　　Next
　　　　Next
　　Next

Debug.? "◆二次元配列の要素"
　　For nY = 1 To UBound(vMatrix)
　　　　For nX = 1 To UBound(vMatrix, 2)
Debug.? vMatrix(nY, nX), ;
　　　　Next nX
Debug.?
　　Next nY
Debug.? "◆"
End Sub
' ' //

デバッグ　と　トレース　を　
しっかりと、出来るようにして、
怠ることなく、ご自分でやるべき仕事、
という意識を持って
（コーディングや質問に）
取り組むようにして下さい。
趣味趣向に副った掃除の仕方を臨時に教えることは出来るでしょうけれど、
実践するのは他の誰でもない、、、でしょう。

Evaluateメソッドは、多重のオーバーヘッドが負担になりますから、
・他に（効率的な）方法が無い場合
・配列（範囲）と配列（範囲）とを演算させる、または演算結果が配列
等、限られた条件の下で、過分の負荷が掛からないよう
扱うように考えた方がよろしいかと。
Worksheetfunctionオブジェクトに用意されている
メソッド（関数）があるならば、
わざわざEvaluateメソッドを用いるメリットはひとつもない処か、
寧ろ処理を遅くしてしまいます。
速さでは大抵他の処理に（例えばベタなループ処理にも）劣るけれど、
人間の立場として早く仕事を片付けたいとか、
文字数というリソースが他より重視される場面とか、
使い処を選んで使えれば、価値が上がってくるのが"Evaluate"ですね。

Evaluateメソッドに、
VBAで変数に格納した配列を
引数として直接渡すことは出来ません。
・セル参照を経由して配列として解釈させるような書き方
　例)
　　Set rng = [a1:c5]
　　matrix = Evaluate("""(""&" & rng.Address(0, 0) & "&"")""")
・配列定数式を文字列として渡す書き方
　例)
　　matrix = Evaluate("{""A"",""B"",""C""}" & "&row(1:3)&" & """=""&" & "{""A"",500,2;""A"",1010,3;""B"",-1000000,2}")
２通りだけですね。

Let の使い方がおかしくないですか？
Property Letに呼応したものなら、今のVBA界でも通じるコトバかもですけれど。
Typeも、それを引数として受け渡しするとか、
配列やコレクションの要素として、仮想テーブルの中のレコード的な扱いなら
アリでしょうけれど、その使い方は却って謎を深めているような気がします。
他、object型にはByValは使いません。誤作動の元です。
object型でないものを格納する時に、
Set ステートメントを用いるのも止めましょう、というより、
object型の参照値なのか、数値や文字列等の値なのか、
結構間違ってること多いみたいなので、
この２つの区別をもっとハッキリ意識した方が、
間違いが減らせて、先が開けてくるんじゃないでしょうか。

　（次の投稿に続きます）

お礼

有難うございます。

ネット等を、調べつつ
少し
読み込ませて
頂いて
また、後日
質問させて、頂ければ
と、思います。