[プログラミング: ExcelVBA] Transpose関数で入れ替えられる要素数を検証してみた
お疲れ様です ( 'ω')ノ
VBAでは、2次元配列の中身を行方向、列方向の入れ替えをするときにTranspose関数が便利です。
Transpose関数は、セルをコピーして貼り付けする際に使用する
”行/列の入れ替え (T)” をVBAで行うための関数です。
この関数はシート上に出力するときだけでなく、2次元配列に対しても有効です。
しかし、このTranspose関数については明記されていない制限があるようで、ハッキリと”○行×○列に対して有効”とか”2次元配列の要素数が○個までは入れ替えができる”とは記述が無いようです。
そこで、独自にどれぐらいまで使えるのかを検証してみました。
＜ 検証環境 ＞
＜ 検証用のコード ＞
Sub WF_Transpose_test()
'===== 変数宣言 =======================================================
Dim i As Long, j As Long
Dim varSample As Variant
Dim lngAryYCnt As Long, lngAryXCnt As Long
Dim lngAryCnt As Long
'====================================================================
'----- 配列の要素数を指定
lngAryYCnt = 100 '行の要素数を指定
lngAryXCnt = 290800 '列の要素数を指定
lngAryCnt = lngAryYCnt * lngAryXCnt '要素数の合計
'----- 配列を再宣言
ReDim varSample(1 To lngAryYCnt, 1 To lngAryXCnt)
'----- 試す
On Error GoTo ErrLine
varAft = WorksheetFunction.Transpose(varSample)
ErrLine:
If Err.Number = 0 Then
Debug.Print _
"要素数：" & Format(lngAryCnt, "#,##0") _
& Format(lngAryYCnt, "(#,##0") & "*" & Format(lngAryXCnt, "#,##0)") _
& vbTab & "正常終了"
Else
Debug.Print _
"要素数：" & Format(lngAryCnt, "#,##0") _
& Format(lngAryYCnt, "(#,##0") & "*" & Format(lngAryXCnt, "#,##0)") _
& vbTab & Err.Number & vbTab & Err.Description
Err.Clear
End If
End Sub
簡単に上記の説明をすると、lngAryYCnt に配列の行にあたる要素数を指定し、lngAryXCnt に列にあたる要素数を指定します。
配列に入れる値の大きさによって結果のバラツキが出ないように配列の各要素の中には何も代入していません。
そして、そのままコードを実行するとイミディエイトウィンドウに配列の要素数合計(行要素数×列要素数)と”正常終了” または エラー番号とエラーメッセージが出力されます。
＜ 実行結果 ＞
実行結果は、以下の通りです。
要素数：27,000,000(1,000*27,000) 正常終了
要素数：28,000,000(1,000*28,000) 正常終了
要素数：29,000,000(1,000*29,000) 正常終了
要素数：30,000,000(1,000*30,000) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,000,000(1,000*29,000) 正常終了
要素数：30,000,000(1,000*30,000) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,500,000(1,000*29,500) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,800,000(1,000*29,800) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,300,000(1,000*29,300) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,000,000(1,000*29,000) 正常終了
要素数：29,200,000(1,000*29,200) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,100,000(1,000*29,100) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,050,000(1,000*29,050) 正常終了
要素数：29,080,000(1,000*29,080) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,070,000(1,000*29,070) 正常終了
要素数：29,075,000(1,000*29,075) 正常終了
要素数：29,078,000(1,000*29,078) 正常終了
要素数：29,079,000(1,000*29,079) 正常終了
要素数：29,080,000(1,000*29,080) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,079,500(100*290,795) 正常終了
要素数：29,079,800(100*290,798) 正常終了
要素数：29,079,900(100*290,799) 正常終了
要素数：29,080,000(100*290,800) 正常終了
要素数：29,080,000(1,000*29,080) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,080,000(1,000*29,080) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,080,000(29,080*1,000) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,080,000(290,800*100) 1004 WorksheetFunction クラスの Transpose プロパティを取得できません。
要素数：29,080,000(100*290,800) 正常終了
厳密にエラーになるか、ならないかの閾値となる要素数を1つずつ探したわけではありません。
ただ、結果としては行×列の要素数が 29,080,000個となるあたりでエラーが発生しています。
しかし、出力された結果を上から見ていくと 要素数：29,080,000個 でも
行1,000 × 列:29,080 の場合と 行:100 × 列:290,800 の場合ではエラーが発生するかどうかが違うようです。
さらに 行:100 × 列290,800 ではエラーになりませんでしたが、
行:290,800 × 列:100 ではエラーになっています。
因みに下がコード実行中のエラーになる直前のタスクマネージャーです。
一時的にではありますが、Excelのプロセスでメモリが2,504MB使用されています。
Excelファイルを開いているだけだと60数MBだったので、2,400MB前後もコードの実行に使用されているようでした。
あと、画像は残していませんでしたが、上で記した実行結果の前に2，3度テストをしていましたが、エラーが発生した要素数を一旦ファイルを閉じてから再度実行した時にエラーが発生せずに正常終了したこともありました。
そのことを踏まえ、タスクマネージャで使用されているメモリを見ていると要素数〇個までTranspose関数で入れ替えができるというよりメモリ使用量との兼ね合いなのではないかと思われます。
また、この検証では配列の各要素には値を代入していません。ですので、各要素の値はEmptyのままになっています。
このTranspose関数では、使用しているPCの性能やExcelが64bit版でも入れ替えできる要素数の上限が変わってきそうです。
＜ 結論 ＞
・PCの性能やその時の使用メモリによる
・配列内にどれぐらいの情報量(値)があるかで変わる
・Excelが64bit版だとExcelで使用できるメモリ容量が変わるので、そこでも変わると思われる
最後まで、記事を読んでいただきありがとうございました！
宜しければ ”スキ” をしてもらえると僕のモチベーションが上がり、また次の記事につなげることができます。
どうぞよろしくお願いします！