セルへの出力で配列を使う場合の注意点
Excelで大量の値を連続したセルに代入したい場合、値を2次元配列に格納しておいて、それを同じサイズのセル範囲のValueプロパティに代入するということはよく行われます。
これは、高速化のテクニックとして非常にポピュラーなものです。
1セルずつ出力しても配列で一気に出力しても処理結果はほとんどの場合は同じですが、常にそうなるとは限りません。
データの長さ次第で、後者に限ってエラーになります。
まずは、同じ処理結果になる場合のサンプルコードです。
Sub TEST() Const cLen As Long = 8203 ' ココ Dim shtA As Worksheet Dim aryX(1 To 1, 1 To 2) Set shtA = Worksheets("Sheet1") shtA.Cells(1, 1).Resize(1, 4).Clear aryX(1, 1) = cLen aryX(1, 2) = String(cLen - 1, "A") & "B" ' # 1セルずつ出力 # With shtA.Cells(1, 1) .Value = aryX(1, 1) .Offset(, 1).Value = aryX(1, 2) End With Debug.Print "1セルずつ出力 無事終了" ' # まとめて出力 # shtA.Cells(1, 3).Resize(1, 2).Value = aryX Debug.Print "まとめて出力 無事終了" End Sub
このコードを
Const cLen As Long = 8204 ' ココ
とすると、以下のエラーが出ます。
実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。
このエラーは、# まとめて出力 #で起こり、その前の# 1セルずつ出力 #は正常に動作します。
つまり、文字列長が8203文字を超える場合には、配列を使って一気に出力することができないということです。
(上記は、Excel2007で試しています。Excel2003以前では長さのリミットはこれよりも短く(911文字)なるようです。)