WorksheetFunction.Transpose関数で起きる不具合全集

Excel VBAには WorksheetFunction.Transpose(セル範囲 または 配列) という 二次元配列を横に倒したり、縦に縦向けたりする便利な関数があります。

コーディングしていると様々な場面で行と列を入れ替えたい場面がやってきます。

1. ReDim Preserve で二次元配列の要素数を変更したいが、二次元目（列）しか増減させられないので、Transposeで行と列を入れ替えてから要素数を変更して、もう一度Transposeで元に戻したい。
2. Dictonary.Items や Split した 一次元配列をセルへ縦に一括で書き出せるように変換したい。

など。

しかし、WorksheetFunctionの関数には見過ごせない問題が多数あるため、本記事では私が認知している全ての問題点を列挙します。

問題一覧

個人的に重大な問題と捉えているのが

• 配列の行数または列数が65537以上でエラーもなく要素数が減る
• 転置結果が1行しかないとき一次元配列になる
• Date(日付型)・Currency(通貨型)のデータはString(文字列型)になる
• (主にAccess等で) Null が混ざっていると実行時エラーが発生する

他にも

• 要素の中に255文字や32768文字を超える長さの文字列があると実行時エラーが発生する
• 要素の合計数が多くなるとエラーが起きるときがある

また、問題というよりは注意しておいたほうが良い点として

• 配列の要素番号の起点が1になる
• 要素が2件以上ある一次元配列は二次元配列になる
• 数値型 は Double（浮動小数点型） になる

などがあります。

解説

配列の行数または列数が65537以上でエラーもなく要素数が減る

一次元目または2次元目の要素数が65537を超えているとき、末尾から 65536件*N周分 が消えてしまうようです。

つまり、次のようになります。

• Dim arr(0 to 65537) や Dim arr(1 to 635537) は Dim arr(1 to 1, 1 to 1)
• Dim arr(1 to 100000) は Dim arr(1 to 1, 1 to 34464)
• Dim arr(1 to 300000, 1 to 10) は Dim arr(1 to 100, 1 to 37856)

Sub 配列の行数または列数が65537以上でエラーもなく要素数が減る()
    Dim arr
    Dim out
    
    ReDim arr(0 To 65537)
    out = WorksheetFunction.Transpose(arr)
    Debug.Print UBound(arr), UBound(out)
    
    ReDim arr(1 To 635537)
    out = WorksheetFunction.Transpose(arr)
    Debug.Print UBound(arr), UBound(out)
    
    ReDim arr(1 To 100000)
    out = WorksheetFunction.Transpose(arr)
    Debug.Print UBound(arr), UBound(out)
    
    ReDim arr(1 To 300000, 1 To 10)
    out = WorksheetFunction.Transpose(arr)
    Debug.Print UBound(arr), UBound(out)
End Sub

転置結果が1行しかないとき一次元配列になる

入力元変数が二次元配列だとしても、結果が1行しか残らないときは一次元配列になります。

また、入力が一次元配列だとしても、要素が1件しかなければ転置後も1行しか残らないため、一次元配列のままです。

Sub 転置結果が1行しかないとき二次元配列が一次元配列になる()
    Dim arrIn(1 To 3, 1 To 1) As Variant
    Dim arrOut As Variant
    arrOut = WorksheetFunction.Transpose(arrIn)
    Debug.Print UBound(arrOut, 1)
    Debug.Print UBound(arrOut, 2) 'error
End Sub

Sub 転置結果が1行しかないとき一次元配列が一次元配列になる()
    Dim arrIn(1 To 1) As Variant
    Dim arrOut: arrOut = WorksheetFunction.Transpose(arrIn)
    Debug.Print UBound(arrOut, 1)
    Debug.Print UBound(arrOut, 2) 'error
End Sub

Date(日付型)・Currency(通貨型)のデータはString(文字列型)になる

Sub Date日付型やCurrency通貨型のデータはString文字列型になる()
    Dim vv
    vv = WorksheetFunction.Transpose(Array(("123123"), ("456")))
    Debug.Print TypeName(vv(1, 1)), vv(1, 1)
    vv = WorksheetFunction.Transpose(Array(CDate("2025/1/1"), CDate("2025/1/2")))
    Debug.Print TypeName(vv(1, 1)), vv(1, 1)
    vv = WorksheetFunction.Transpose(Array(CCur("\123,123"), CCur("456")))
    Debug.Print TypeName(vv(1, 1)), vv(1, 1)
    'String        123123
    'String        2025/1/1
    'String        \123,123
End Sub

Null が混ざっていると実行時エラーが発生する

Excelで Null が使われることは滅多にありませんが、例えばAccessからExcelのライブラリを使ったり、ExcelからAccessのデータを扱っているときに発生します。

データベースにはデータが本当に【なにもない】ことを表す Null 状態が存在するため、Excelには概念ということでエラーになります。

Sub Nullが混ざっていると実行時エラーが発生する()
    Dim arrIn(1 To 3, 1 To 2) As Variant
    arrIn(2, 1) = Null      ' ← Null を混入
    Dim arrOut As Variant
    arrOut = WorksheetFunction.Transpose(arrIn) 'error
    Rem  エラー 13 型が一致しません。
End Sub

通常は上記のようにNullを代入することはないため、 Recordset.GetRows() などで取り出したデータで発生します。

要素の中に255文字や32767文字を超える長さの文字列があると実行時エラーが発生する

Excel 2010では再現するのですが、最新の Microsoft 365 Excel バージョン 2508では発生しないため、Excelのバージョン2013～2021のどこかで修正済みと考えられます。

なお、バージョン 2508では仕様に書かれている最大値の32767文字を超えるとエラーが発生しました。

Excel の仕様と制限 - Microsoft サポート

Sub 要素の中に255文字を超える長さの文字列があると実行時エラーが発生する()
    Dim arrIn(1 To 2, 1 To 2) As Variant
'    arrIn(1, 2) = String(256, "A") ' ← 旧バージョンエラー下限
    arrIn(1, 2) = String(32768, "A") ' ← 新バージョンエラー下限
    
    Dim arrOut As Variant
    arrOut = WorksheetFunction.Transpose(arrIn)
End Sub

ちなみに、セルに直接書き込む場合は限界を超えた文字数でもエラーは起きません。無言で先頭の32767文字だけ書き込んで素通りします。

Range("A1") = String(32767, "A") & "ここから先は消える"

要素の合計数が多くなるとエラーが起きるときがある

要素数と変数内のデータ量が大きくなるほど実行時エラーが起きやすくなります。

Sub 配列要素数耐久テスト()
    Dim arrIn() As Variant
    Dim arrOut As Variant
    Dim rr As Long, cc As Long
    
    For cc = 310 To 1000 Step 10
        ReDim arrIn(1 To 65536, 1 To cc)
        Rem 113列, 291列, 310列 でエラーがおきたが休憩すると上限が増える。
        Rem 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
        arrOut = WorksheetFunction.Transpose(arrIn)
    Next
End Sub

Excel バージョン 2508 32bit版 の環境下では、空の変数では 65536 * 113列 で最初の実行時エラーが発生しました。

おそらく、PC及び1プロセスで扱えるメモリの容量の影響も受けると思います。

（ちなみにエラーが出る度に時間を空けて続きから実行するのを繰り返すことで310列まではいけました）

100列ある表データで65536行をまるごと転置することは早々ないので大丈夫だとは思いますが、環境によってはもっと小さな規模で起きるかもしれません。

複合的問題例

Date(日付型)・Currency(通貨型)のデータはString(文字列型)になる

という性質を組み合わせて発生する複合的問題に遭遇したため紹介します。

VBAでは、書式設定が 標準 や 数値 のセルに文字列を書き込むとき、数値や日付に見える文字列は暗黙的に数値・シリアル値として書き込まれますが、通貨を表現すうる文字列（ "\123,456" ）だけは文字列としてそのまま書き込まれます。

文字列として書き込まれたセルは、SUM等の集計から漏れてしまいます。

このような問題が発生した理由は、元データを扱っているユーザーが見やすくするために書式設定を通貨に変更したのが原因でした。

通貨に変更されたセルを arr = Range("A1:Z1000").Value のようにして読み込むと、内部の方は通貨型（Currency）になってしまいます。

この配列をTransposeしたことで、内部データが通貨表記の文字列型に変化して、先の性質により書き出した結果も文字列になってしまいました。

このように、プログラムもデータ構造も変えていないにも関わらず不具合を引き起こす原因となります。

Transposeを使わないのが一番だと思いますが、二次元配列へ取り込む段階で強制的に数値型で格納されるValue2プロパティを使うという対策もあります。

（ちなみに、書き込みでValue2を使っても効果はありません）

皆さんは、くれぐれもTransposeにはご注意ください。

補足：Index関数でも通貨の文字列化は起きる

なお、WorksheetFunction.Index関数でも通貨型が文字列になる現象は発生します。（WorksheetFunctionの関数の中には、他にも同様の動きをするものがあるかもしれません）

要素の開始番号が1ベースになる現象や、大きなデータの欠落が起きる現象など、ほとんどの問題はIndexでも発生するため、Transpose固有の問題ではないという点を補足しておきます。

まとめ

以上の通りWorksheetFunction.Transposeには様々な問題がありますので、開発されているプログラムで不具合を起こさないためにも、行と列を入れ替える関数は自作することをおすすめします。

ネットには有用な代替関数が流通しておりますので、そちらをコピーして使用するのも良いと思いますし、簡単な関数なので練習に作ってみても良いと思います。

ちなみに、ワークシート上で使用するTRANSPOSE関数では本書のような問題は発生しませんので気にせずにご利用ください。（また違った制限があります）

それではよきVBAライフを！

関連参考記事

www.limecode.jp

www.limecode.jp

www.softex-celware.com

misora05.hatenablog.com