配列をTransposeしたらちょん切れたので検証と回避方法を模索

配列をTransposeしたらなんかおかしくなったので、その検証と回避方法をいくつか考えてみました。検証結果は　
配列をTransposeする場合、要素数が65,536を超えるとおかしくなる。
でした。
素人検証なので、確実とは言い切れません。もし、本記事に認識間違いなどあれば、コメントにてご指摘いただけるとありがたいです。
項目１はあーでもない、こーでもないとだらだら検証した軌跡なので、それはいいから回避方法だけ知りたいという方は項目１はすっ飛ばして項目２だけをお読みください。

１．配列をTransposeした時の限界の検証
２．回避方法：Transposeしないて縦横変換する

１．配列をTransposeした時の限界の検証

エクセルで配列を多用する。
ワークシートに大量の書き込みをするときに、シートに1セル1セル書き込みをすると、ものすごく時間がかかるので、配列にデータを入れ込んでおいて一気にワークシートにがばっと入れる、というのをよくやる。
※配列を利用してワークシートに出力する方法についてはこちらに書いています
【Excel/VBA】高速！ワークシートに出力するにはデータを配列に入れて一気に出力がおススメ！

何らかの条件がある場合など、配列の要素数が分からないので、ReDimして配列の要素数を増やしながら、データを配列に蓄積していく。この時、最後の次元しか変更できないため、vbaの2次元配列は2番目の要素数しか変更できない。そうすると、横方向に配列が伸びていくので、ワークシートに入れるときに縦横変換してから出力する。
縦横変換はワークシート関数のTransposeを使います。

こんな感じ↓

Range("A1:B" & endrow).Value = WorksheetFunction.Transpose(arr)

先日トランスポーズしたらなんかおかしくなったので、それを検証してみました。

（１）10万行のデータで検証

1シート目に2列、10万行のデータがあります。A列は1からの連番となっています。

2列10万行をデータを配列arrに代入。Transposeして2シート目に出力します。
最初っから要素数10万ってすればいいじゃんとという突っ込みはナシで。検証用なので。

Sub test1()
    Dim arr() As Variant
    Dim i As Long
    Dim idx  As Long: idx = 0
    '1シート目のデータを配列arrに代入
    With Worksheets(1)
        For i = 1 To 100000
            '2番目の要素を増やす
            ReDim Preserve arr(1, idx)
            arr(0, idx) = .Cells(i, 1).Value
            arr(1, idx) = .Cells(i, 2).Value
            idx = idx + 1
        Next i
    End With
    
    Stop
    
    '2シート目に配列arrの内容を出力
    Dim rowEnd As Long: rowEnd = UBound(arr, 2) + 1
    With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(rowEnd + 1, 2)).Value = WorksheetFunction.Transpose(arr)
    End With

End Sub

Stopの個所で配列の中身をローカルウインドウで見てみます。
10万までちゃんと入っていることが確認できます。

出力結果。。。おかしいんです。
34464でちょんぎれて次のデータがB列の1行目から始まっています。

いったいどうゆうことでしょう。

（２）４万行で検証

34,464が限界なのか？と、読み込みを4万行までにしてみることにしました。

For i = 1 To 40000

すると！ちゃんとでました。34,464が限界ではないようです。

（３）昔のエクセルの行の限界 65,536行で検証

では、昔のエクセルの限界。65,536行まででやってみました。
→正しく出ました。

For i = 1 To 65536

では、一つ増やして65,537でやってみます。
→　は？？？？　という結果に。おかしいです。

途中でとめて、arr2という配列を新たに作り、arr2に配列arrをtransposeして、ローカルで見てみました。
arr2には1つずつしか要素が入っていないことが確認できました。が意味が分かりません。

一つずつ増やしてやってみました。
65,538行、65,539行、65,540行でやった結果が下図のようになりました。

どうやら、65,536を超えると、一度プッツンとなって仕切り直しを勝手に始める？
65,536行を超えると、65,536行との差分しか配列が作成されていないように思われます。
65,540行やってみたとき、arrを配列arr2にtransposeしてローカルで見たものがこちら↓

10万行でやったとき、34,464で折り返しがされていました。10万-65,536=34,464なので、65,536を超えるとプッツンして折り返す説、なんか筋が通っている。

しくみはよくわからないが、65,536を超えた配列をTransposeするとおかしくなるので要注意！ということでしょうか。

２．回避方法：Transposeしないて縦横変換する

それならば、ということで回避方法を考えてみました。なんせ、配列に一度入れてワークシートに一気に入れる時短はなくてはならないものなので。

（１）配列を大きめに宣言しておく

回避方法１：配列の1次元目の要素数を大きめに宣言しておき、そこに入れていく

メリット：簡単
デメリット：予想がつかないときは使えない

都度Redimしていく手法だと、2次元目の要素数しか変更できない。ならば、最初にこれ以上はいかないだろうという大きめの要素数を宣言して配列を用意してく。
先ほどのエクセルデータを50万行まで増やして、A列の数値が2で割りきれるデータだけ配列に格納し、2シート目に出力することとする。(100万行でやってみたかったけど、パソコンの性能が怪しいので50万行としました)
コードはこちら。ついでに開始時、配列作成終了時、データ出力完了時の時刻も計ってみた。

Sub kaihi1()
    Debug.Print "開始:" & Now
    
    Dim arr(500000, 1) As Variant
    
    '1シート目のデータを一旦配列tmpに代入
    Dim tmp As Variant
    With Worksheets(1)
        tmp = .Range("A1:B500000").Value
    End With
    
    'tmpの1次元目の値が2で割り切れる場合、配列arrに入れる
    Dim i As Long
    Dim idx As Long: idx = 0
    For i = 1 To 500000
        If tmp(i, 1) Mod 2 = 0 Then
            arr(idx, 0) = tmp(i, 1)
            arr(idx, 1) = tmp(i, 2)
            idx = idx + 1
        End If
    Next i
    
    Debug.Print "配列作成終わり:" & Now
    
    '2シート目に配列arrの内容を出力
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(idx, 2)).Value = arr
    End With
    
    Debug.Print "終了:" & Now

End Sub

時間は全部で1秒足らず。
開始:2022/07/17 17:05:41
配列作成終わり:2022/07/17 17:05:41
終了:2022/07/17 17:05:42

まず、配列を宣言します。50万行以上はいかないだろうということで1次元目は50万、2時限目は2列持ちたいので「1」

Dim arr(500000, 1) As Variant

要素数に変数を使いたい場合等、ReDimで宣言するのもOK。
以下、変数cntを使って、配列の宣言をする例。
最初に Dim arr() As Variant と要素数を入れずにの配列をする。
その後変数cntに数値を代入し、そのcntでReDimする。
このように、最初のRedimで1次元目の要素数を大きくとることはできますが、そのあと、1次元目を変更しようとすると、エラーになります。

Dim arr() As Variant
Dim cnt As Long
With Worksheets(1)
    cnt = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
ReDim arr(cnt, 1)

処理が遅くなりそうなので、最初に配列tmpにシート1のデータを入れました。

'1シート目のデータを一旦配列tmpに代入
Dim tmp As Variant
With Worksheets(1)
    tmp = .Range("A1:B500000").Value
End With

その後tmpを参照して、配列を作成していきます。
ちょっと注意するのはtmpは「1」始まりの配列、arrは「0」始まりの配列です。

'tmpの1次元目の値が2で割り切れる場合、配列arrに入れる
Dim i As Long
Dim idx As Long: idx = 0
For i = 1 To 500000
    If tmp(i, 1) Mod 2 = 0 Then
        arr(idx, 0) = tmp(i, 1)
        arr(idx, 1) = tmp(i, 2)
        idx = idx + 1
    End If
Next i

配列作成後にStopを入れています。ここで止めて、配列arrの中身を見てみると、2で割り切れる数のみ入れているので用意した要素数50万の半分の25万以降はEmptyでデータが入っていないことが分かります。

今度は縦長の配列なのでTranposeの必要なし、そのままシート2に出力します。
出力の最終行は500000としてもよいのですが、idxがちょうど最終行と一致するはずなので、idxを利用しています。

'2シート目に配列arrの内容を出力
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(idx, 2)).Value = arr
End With

シート2には偶数のデータのみ出力されました。

（２）横長の配列を作成し、1要素ずつ縦長の配列に入れなおす

回避方法２：横長の配列を作成し、Transposeせず、一つずつ縦長の配列に入れなおす。

メリット：簡単、無駄に大きめの配列を用意する必要がない。
デメリット：なんか長ったらしい

都度Redimしていく手法で、いったん横長の配列を作成し、Tranposeを使わず、手動で一つ一つ縦横変換する。

Sub kaihi2()
    Debug.Print "開始:" & Now
    
    '1シート目のデータを一旦配列tmpに代入
    Dim tmp As Variant
    With Worksheets(1)
        tmp = .Range("A1:B500000").Value
    End With
    
    'tmpの1次元目の値が2で割り切れる場合、配列arrに入れる
    Dim arr() As Variant
    Dim i As Long
    Dim idx As Long: idx = 0
    For i = 1 To 500000
        If tmp(i, 1) Mod 2 = 0 Then
            ReDim Preserve arr(1, idx)
            arr(0, idx) = tmp(i, 1)
            arr(1, idx) = tmp(i, 2)
            idx = idx + 1
        End If
    Next i
    
    Debug.Print "配列arr作成終わり:" & Now
    
    '配列arrの最大要素数
    Dim maxidx As Long
    maxidx = UBound(arr, 2)
    '配列arr2を用意
    Dim arr2() As Variant
    ReDim arr2(maxidx, 1)       '配列arrの最大要素数で再宣言
    '配列arr→配列arr2に移し替え
    For idx = LBound(arr, 2) To UBound(arr, 2)
        arr2(idx, 0) = arr(0, idx)
        arr2(idx, 1) = arr(1, idx)
    Next idx
    
    Debug.Print "配列arr2作成終わり:" & Now
    
    '2シート目に配列arr2の内容を出力
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(maxidx + 1, 2)).Value = arr2
    End With
    
    Debug.Print "終了:" & Now

End Sub

時間は配列arr作成に6秒ほどかかっていますね。ReDimすると時間がかかるのか。
でも、遅くはないですね。
開始:2022/07/17 17:38:58
配列arr作成終わり:2022/07/17 17:39:04
配列arr2作成終わり:2022/07/17 17:39:04
終了:2022/07/17 17:39:05

シート1のデータをいったんtmpに入れるのは前項と同じ。
tmpの内容を配列arrに入れていきます。一つ要素が増えるごとにReDimし、2時限目の要素数を追加しています。この段階では横長の配列になります。

'tmpの1次元目の値が2で割り切れる場合、配列arrに入れる
Dim arr() As Variant
Dim i As Long
Dim idx As Long: idx = 0
For i = 1 To 500000
    If tmp(i, 1) Mod 2 = 0 Then
        ReDim Preserve arr(1, idx)
        arr(0, idx) = tmp(i, 1)
        arr(1, idx) = tmp(i, 2)
        idx = idx + 1
    End If
Next i

次に、横長の配列arrを縦長の配列arr2に移していきます。
arr2の要素数を宣言します。arrの2次元目の要素の最大値をarr2の1次元目の最大値として宣言します。

'配列arrの最大要素数
    Dim maxidx As Long
    maxidx = UBound(arr, 2)
'配列arr2を用意
    Dim arr2() As Variant
    ReDim arr2(maxidx, 1)       '配列arrの最大要素数で再宣言

arr（要素1,要素2）をarr2（要素2,要素1）と、次元を入れ替えてデータを移植します。

'配列arr→配列arr2に移し替え
For idx = LBound(arr, 2) To UBound(arr, 2)
    arr2(idx, 0) = arr(0, idx)
    arr2(idx, 1) = arr(1, idx)
Next idx

arr2をシート2に出力します。縦長なのでTransposeの必要はありません。

'2シート目に配列arr2の内容を出力
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(maxidx + 1, 2)).Value = arr2
End With

（３）データをコレクションに蓄積し、コレクションから配列にデータを移す

回避方法3：コレクションにデータを蓄積し、コレクションのデータを配列に入れる

メリット：無駄に大きめの配列を用意する必要がない。早い（気がする）
デメリット：ちょっと複雑。

コレクションにいったんデータを蓄積し、コレクションのデータを配列に入れていれていく方法。
コレクションをワークシートに一気に入れられたら良いのですが、できない（と思う）ので、ワークシートに一気にデータを出力できる技量を持つ配列先輩にデータを渡し、配列をシートに出力する。

Sub kaihi3()
    Debug.Print "開始:" & Now
    
    '1シート目のデータを一旦配列tmpに代入
    Dim tmp As Variant
    With Worksheets(1)
        tmp = .Range("A1:B500000").Value
    End With
    
    'tmpの1次元目の値が2で割り切れる場合、配列コレクションに入れる
    Dim clctDatas As Collection: Set clctDatas = New Collection
    Dim i As Long, tmparr(1) As Variant
    For i = 1 To 500000
        If tmp(i, 1) Mod 2 = 0 Then
            tmparr(0) = tmp(i, 1)
            tmparr(1) = tmp(i, 2)
            clctDatas.Add tmparr, CStr(i)
        End If
    Next i
    
    Debug.Print "コレクション作成終わり:" & Now
    
    'コレクションの最大要素数
    Dim maxidx As Long
    maxidx = clctDatas.Count - 1
    '配列arrを用意
    Dim arr() As Variant
    ReDim arr(maxidx, 1)     'コレクションの要素数で再宣言
    'コレクション→配列arrに移し替え
    Dim cData As Variant, idx As Long
    idx = 0
    For Each cData In clctDatas
        arr(idx, 0) = cData(0)
        arr(idx, 1) = cData(1)
        idx = idx + 1
    Next
    
    Debug.Print "配列arr作成終わり:" & Now
    
    '2シート目に配列arrの内容を出力
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(maxidx, 2)).Value = arr
    End With
    
    Debug.Print "終了:" & Now

End Sub

時間はまあ、早いっすね。2秒足らずで終了。
開始:2022/07/17 17:56:29
コレクション作成終わり:2022/07/17 17:56:30
配列arr作成終わり:2022/07/17 17:56:30
終了:2022/07/17 17:56:31

シート1のデータをいったんtmpに入れるのは前項、前々項と同じ。
tmpの内容をコレクションclctDatasに入れていきます。
コレクションclctDatasを宣言し、Newします。
tmparrという要素数2の一次元配列を用意します。
2で割り切れるデータの場合、いったん用意したtmparrにA列のデータ、B列のデータを格納します。
コレクションclctDatasに追加します。値はtmparr、キーは i を文字列変換したものを入れています。

'tmpの1次元目の値が2で割り切れる場合、配列コレクションに入れる
Dim clctDatas As Collection: Set clctDatas = New Collection
Dim i As Long, tmparr(1) As Variant
For i = 1 To 500000
    If tmp(i, 1) Mod 2 = 0 Then
        tmparr(0) = tmp(i, 1)
        tmparr(1) = tmp(i, 2)
        clctDatas.Add tmparr, CStr(i)
    End If
Next i

コレクション作成ができたら、CountでコレクションclctDatasの要素数を取得します。配列は0始まりにするので、これから１を引いた数をmaxidxに代入し、これを利用し配列を宣言します。

'コレクションの最大要素数
Dim maxidx As Long
maxidx = clctDatas.Count - 1

'配列arrを用意
 Dim arr() As Variant
 ReDim arr(maxidx, 1)     'コレクションの要素数で再宣言

コレクションを配列arrに移し替えます。
For Each でclctDatasの要素を一つずつ cData に取り出します。
値は配列として格納しているので、cData(0)で格納した配列データの1番目(A列のデータ）が取り出しできます。
cData(0)をarr(0)に代入します。
どうように、cData(1)で格納した配列データの2番目(B列のデータ)を取り出し、arr(1)に代入します。
配列idxを1増やします。

'コレクション→配列arrに移し替え
Dim cData As Variant, idx As Long
idx = 0
For Each cData In clctDatas
    arr(idx, 0) = cData(0)
    arr(idx, 1) = cData(1)
    idx = idx + 1
Next

ここは前項と同じです。シート2に配列arrを出力します。
出力の最終行はコレクションの要素数と同じになるので、変数maxidxを利用しています。

'2シート目に配列arrの内容を出力
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(maxidx, 2)).Value = arr
End With

※コレクションのデータは入れた順に出てくるものと信じているのでForEachを使って取り出ししています。もし、おかしいんだけど！となった場合は、コレクション作成時にキーに順番を表すインデックス番号を代入しておいて、インデックス順に出すと順番が保証されます。

一応、そのように書き換えたコードはこちら。
コレクション作成時にcntという変数を利用し、コレクション追加時にキーとして持っておく。コレクション追加ごとに1プラスする。コレクションはキーが文字列でなければいけないのでCStrで文字列変換して代入しています。
配列arrに入れ替えするときに変数cntを1から、コレクションの最大要素数まで順番に回します。
cData = clctDatas(CStr(cnt))　でcntに対応するコレクションデータからcDataに値を取り出します。値cDataは配列です。

Sub kaihi3_2()
    Debug.Print "開始:" & Now
    
    '1シート目のデータを一旦配列tmpに代入
    Dim tmp As Variant
    With Worksheets(1)
        tmp = .Range("A1:B500000").Value
    End With
    
    'tmpの1次元目の値が2で割り切れる場合、配列コレクションに入れる
    Dim clctDatas As Collection: Set clctDatas = New Collection
    Dim i As Long, tmparr(1) As Variant
    Dim cnt As Long: cnt = 1        'コレクションに入れた順番をキーに格納
    For i = 1 To 500000
        If tmp(i, 1) Mod 2 = 0 Then
            tmparr(0) = tmp(i, 1)
            tmparr(1) = tmp(i, 2)
            clctDatas.Add tmparr, CStr(cnt)
            cnt = cnt + 1
        End If
    Next i
    
    Debug.Print "コレクション作成終わり:" & Now
    
    'コレクションの最大要素数
    Dim maxidx As Long
    maxidx = clctDatas.Count - 1
    '配列arrを用意
    Dim arr() As Variant
    ReDim arr(maxidx, 1)     '配列arrの最大要素数で再宣言
    'コレクション→配列arr2に移し替え
    Dim cData As Variant, idx As Long
    idx = 0
    For cnt = 1 To clctDatas.Count
        cData = clctDatas(CStr(cnt))
        arr(idx, 0) = cData(0)
        arr(idx, 1) = cData(1)
        idx = idx + 1
    Next
    
    Debug.Print "配列arr作成終わり:" & Now
    
    '2シート目に配列arrの内容を出力
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(maxidx, 2)).Value = arr
    End With
    
    Debug.Print "終了:" & Now

End Sub

時間はForEachの方が早いと聞いていましたが、変わらないですね。まあ、50万行だし、一回しか計測してないからあれですが。
開始:2022/07/18 9:42:17
コレクション作成終わり:2022/07/18 9:42:18
配列arr作成終わり:2022/07/18 9:42:18
終了:2022/07/18 9:42:19