Transposeは65,537番以降を無告知で破棄する

知らずに落ちると抜け出せなくなるVBAの落とし穴です。

• Transpose関数がうまく配列を変換してくれない
• 配列をセルに出力したとき、なぜか#N/Aエラーになる

あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

Transpose関数の対応要素数上限は65,536

端的に、Transpose関数にはタイトルの通りの要素数上限65,536があります。

例えば以下のコードを実行してみます。

Sub Transpose上限テスト()

    ' 10万の連番が入った配列を作成
    Dim Arr(1 To 100000)
    
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = i
    Next
    
    ' Transpose関数でセルに出力
    Range("A1:A100000").Value = WorksheetFunction.Transpose(Arr)
    
End Sub

　
一次元配列を縦方向に一括出力するおなじみのコードですが、
以下の画像の通り見事に後半部分は#N/Aになってしまいます。

上限があるのはまあしょうがないとして、
タイトルの通り無告知、すなわちエラーを出してくれません。

これがかなり厄介な仕様で、この現象を知らないとどこで何が起きたかもわからず、
バグが迷宮入りしかねません。

この仕様は知識として知っておきましょう。

この現象、面白いのが65,537番から先が#N/Aになっている訳でなく、

こんな風に34,464番を最後に#N/Aとなっています。

しかしDim Arr(1 To 65536)で実行してみると、

正常実行できています。

しかしここで65537と上限を1オーバーさせてみると、

突如すべてのセルに「1」を出力する暴挙に出ます。

この現象を謎解くカギは二次元配列での実行結果にあり、

Sub Transpose上限テスト2()

    Dim Arr()
    ReDim Arr(1 To 2, 1 To 100000)
    
    Dim i As Long
    For i = LBound(Arr, 2) To UBound(Arr, 2)
        Arr(1, i) = i
        Arr(2, i) = "a"
    Next

    Range("A1:C100000").Value = WorksheetFunction.Transpose(Arr)

End Sub

　
このような「1列目が10万番までの連番、2列目がすべて"a"」という配列で試すと、

こんな風に34,464番で謎の折り返しを始めた形跡を見ることができます。

それはいいんだけど"a"さんはどこ行ったの？

ということで、この謎仕様はどうでもいいのですが、
Transposeを使用する際は1次元あたり65,536以上にならないよう注意してください。

解決策

Transpose関数の使い道は大きく以下の2つになると思います。

1. 一次元配列を縦方向にセル出力するためn行1列の二次元配列に変換する
2. n行m列の二次元配列をm行n列の二次元配列に変換する

両方の解決策を記載します。

一次元配列をセル出力するためにn行1列の二次元配列に変換する

この2つの使い道で65,536要素を超えるのはほぼこちらのケースと思います。

まずはその解決策ですが、以下の通り愚直に配列に代入して対応します。

' Transpose上限対応用関数
Function GetArray一次元配列→n行1列の二次元配列(Arr As Variant) As Variant
    
    Dim 生成配列()
    ReDim 生成配列(LBound(Arr) To UBound(Arr), 1 To 1)
    
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        生成配列(i, 1) = Arr(i)
    Next
    
    GetArray一次元配列→n行1列の二次元配列 = 生成配列
    
End Function

' 上記の関数を使ってセルへの出力コードを以下の通り書き替える
出力セル範囲.Value = WorksheetFunction.Transpose(Arr出力配列)
    ' ↓ 書き替え
出力セル範囲.Value = GetArray一次元配列→n行1列の二次元配列(Arr出力配列)

単純に一次元配列の全要素をひとつずつ二次元配列に代入していくコードです。

大変そうに見えますが、処理速度はTranspose関数と同速ですのでご安心ください。

この手のコードをいちいち書くのは手間なので、
上記のように汎用関数にして持っておきましょう。

さらにいうと、目的はセルへの出力なわけですから、

' 一次元配列 → セル
Sub 一次元配列をセルに出力する(出力始点セル As Range, Arr出力配列 As Variant)
    
    Dim 要素数 As Long: 要素数 = Count配列の要素数(Arr出力配列)
    出力始点セル.Resize(要素数, 1).Value = GetArray一次元配列→n行1列の二次元配列(Arr出力配列)

End Sub

このように出力まで関数にしてしまった方がより便利だと思います。

配列をセルに出力する詳しい解説はこちらの記事をご覧ください。
www.limecode.jp

n行m列の二次元配列をm行n列の二次元配列に変換する

こちらは行列を逆転する本来のTransposeの使い方です。

と言いたいところなのですが、ワークシートの列数は最大16,384列であり、
すべての列をフルで使っても65,536には到底及びません。

ワークシート関数をシート上限を超えて使うというのも、
それはそれで本来の使い方ではない気がしますね。

なのでこちらの使い方の方がめったにない気がしますし、
実際私は今までこの問題に直面したことがありません。

解決策は愚直に代入して反転させるより他ないと思います。
一応関数を置いておきますので、もし必要であればお使いください。

Function TransposeEx(Arr As Variant) As Variant
    
    Dim 生成配列()
    ReDim 生成配列(LBound(Arr, 2) To UBound(Arr, 2), LBound(Arr, 1) To UBound(Arr, 1))
    
    Dim i As Long
    Dim j As Long
    
    For i = LBound(Arr, 2) To UBound(Arr, 2)
        For j = LBound(Arr, 1) To UBound(Arr, 1)
            生成配列(i, j) = Arr(j, i)
        Next
    Next
    
    TransposeEx = 生成配列
End Function