【VBA】配列とWorksheetFunction.Transpose

Question

前任者から引き継いだマクロファイルがあり、VBA初心者ながらコードを理解しようとしています。
質問は下記2行の記述についてです。（ｖはバリアント型変数です）

v=array(WorksheetFunction.Transpose(range("a1:a1000")), WorksheetFunction.Transpose(range("c1:c1000")))
v=WorksheetFunction.Transpose(v)
worksheets(2).cells(1,1).resize(1000,2)=v

１～２行目で、Ａ１：Ａ３とＣ１：Ｃ３を二次元配列として変数vに格納して、３行目で隣のシートに配列の中身をコピーしています。

なぜこういう形でTransposeを２回（１行目と２行目）かけると二次元配列になるのでしょうか？
いきなりv=array(range("a1:a1000"), range("c1:c1000"))じゃダメなんでしょうか。

Transposeは配列の行列を逆にする関数なんですよね。
２行目実行時点では変数ｖの中身は二次元配列が入っているので、
１行目実行時点では、それと行列が逆の二次元配列が入っているのかと思ったのですが、違いました。
１行目を実行しただけだと、変数ｖには要素数２個の一次元配列が格納されているようです。
それにTransposeをかけたら、２行×１列の二次元配列になりそうな気がするのですが、どうしてそうならないのでしょうか。

分かりにくい説明で申し訳ありません。
どなたかご教授いただけると幸いです。

WindFaller · Accepted Answer

こんにちは。

私の解答は視点が違いますが、Transpose を多用するというのは、実際は、パターン化できれば良いかもしれませんが、思い出しながら、書くというのは、ちょっと面倒な気がします。

Transpose 関数というのは、縦のものを横にすると、２次元から1次元に落とす働きを持つ関数です。横のものを縦にすると1次元に変わるのが、Index関数です。このようなテクニックは、Ver.4時代に培われたもののようです。

<サンプルは、全て規模を縮小します。>

With Application
a = .Transpose(Range("A1:A10").Value)　'縦を横にして１次元
b = .Index(Range("A1:J1").Value, 1, 0)　'横を縦にして１次元
End With

これは、両方共1次元に変わります。
これについては、Ubound を使わなくても、一目瞭然だと思いますが……。

だから、一旦縦横を替えたものを、もう一度使えば、元の縦横に戻りながら、１次元のデータが得られるわけです。

以下は、配列ではありません。PCの性能にも依存してきますから、必ずしも、ベストではありませんが、ずっと変わらないまま残る関数テクニックと、時代とともに廃れてしまう書き方もあるような気がします。スピードを速めるために使ったテクニックもPCの性能が上がれば、不要なものもあります。

>v=array(range("a1:a1000"), range("c1:c1000"))
この場合は、このようにすればよいはずです。

Sub Test2()
　 v = Array(Range("A1:A10"), Range("C1:C10"))
　 v(0).Copy Range("A11")
　 v(1).Copy Range("B11")
End Sub

Sub Test3()
　 Set Rng = Union(Range("A1:A10"), Range("C1:C10"))
　 Rng.Copy Range("A11")
End Sub

なお、Transpose 等の関数の配列の限界数は、以前、書いたことがありますが、Ver. 2007にはなくなったように、Microsoft サポートでは読めますが、Transpose関数の限界数は、Ver.2007で、65,536(=2^16) まででした。現在、手元のExcel 2013で見る限りは、Transpose には、フルレインジ[Range("A1",Cells(Rows.Count,1)).Value ]まで扱っても問題は発生しません。

https://support.microsoft.com/ja-jp/help/166342/description-of-the-limitations-for-working-with-arrays-in-excel

めぐみん_ · Answer

No.1の補足に対して。

＞なぜtransposeによって二次元に変わるのか？
私個人は変わったのはあくまで行・列であって、一次元が二次元に変わったとは思ってません。
１行目について言えばですけど。
配列の配列も結局は行・列が存在するものであって、それを一次元と言えるのでしょうか？
ちょっと疑問に感じました。

30246kiku · Answer

Transpose は、そういう動きをするもの････と思うしかないかも

以下で、ステップ実行しながら、各変数がどのような配列になるか･･･
確認してみるしか････

ちなみに、v2 と vC は同じになります

Sub t()
　　Dim vA As Variant, vB As Variant, vC As Variant
　　Dim v1 As Variant, v2 As Variant, v3 As Variant

v1 = Range("A1:A5").Value
　　v2 = Range("A1:B5").Value
　　vA = WorksheetFunction.Transpose(v2)

v3 = Range("B1:B5").Value
　　With WorksheetFunction
　　　　vB = Array(.Transpose(v1), .Transpose(v3))
　　　　vC = .Transpose(vB)
　　End With
End Sub

なお、Transpose には動作上限がある？みたいで、
以下での返信 2017/12/18 09:12:53 も併せて確認してみるとか

VBA で検索速度を早くする方法はありませんか？
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11183386916

めぐみん_ · Answer

詳しい訳ではないですが。

＞１行目を実行しただけだと、変数ｖには要素数２個の一次元配列が格納されているようです。
Excelのセルは行列が存在するので、１次元配列になる事はありません。
それは

Dim v As Variant
v = Range("A1:A5")
Debug.Print UBound(v, 1), UBound(v, 2)

結果：　 5             1

行数が５、列数が１である事で検証出来ると思います。
UBound(v,2)を無視してUBound(v)としてしまえば１次元のように思うでしょう。
けど、

Debug.Print v(3)
ではエラーになり、

Debug.Print v(3, 1)
ではきちんと値を表示してくれます。

１行目でやっているのは、離れた列の値をそれぞれ行列入れ替えて結合しているのでは？
⇒範囲外のB列の値を省きたいが為に

Dim v As Variant

v = Array(WorksheetFunction.Transpose(Range("A1:A5")), WorksheetFunction.Transpose(Range("C1:C5")))
v = WorksheetFunction.Transpose(v)
Debug.Print UBound(v, 1), UBound(v, 2)

結果： 5             2

行数５、列数２になります。
なぜこうなるのか？
自分も疑問はありますが、Arrayによって仮想シートが作られている・・・みたいな？

仮にArrayでTransposeしなければ、値の取得には

Debug.Print v(0)(3), v(1)(3)

のようなジャグ配列（配列の配列）となり結果的にセルへの一括書き出しは難しくなりそう。

と、素人的にはみています。

正確な理由は経験豊富な方にお任せですかね。

【VBA】配列とWorksheetFunction.Transpose

こんにちは。

No.1の補足に対して。

Transpose は、そういう動きをするもの････と思うしかないかも

詳しい訳ではないですが。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング