1. 導入:なぜループ処理での代入は遅いのか?
実務で数千、数万行のデータを扱う際、For文を使って「Cells(i, 1).Value = データ」のように1行ずつ値を代入していませんか?実は、これこそがマクロの動作を重くする最大の要因です。VBAからExcelシートへの書き込みは、一度発生するごとにシステム負荷がかかります。配列を使ってデータをメモリ上で処理し、最後に一括でセルへ書き出すことで、劇的に処理速度を向上させることが可能です。
2. 基礎知識:配列と一括代入の仕組み
配列とは、メモリ上に作成された「データの箱の列」です。通常、セル範囲を操作する際は「Range」や「Cells」を使いますが、これらをループで回すと「セルへのアクセス」回数が膨大になります。
一方、配列にデータを格納して「Range(“A1”).Resize(行数, 列数).Value = 配列」と記述すると、Excelはメモリ上のデータを一瞬でシート上に展開します。これにより、数秒〜数分かかっていた処理が、一瞬で終わるようになります。
3. 実装/解決策:2次元配列の活用
縦方向にデータを流し込む場合、VBAでは「2次元配列(行, 列)」を使用するのが鉄則です。
・1次元配列:横方向(1行)のデータに適している
・2次元配列:縦方向(複数行)のデータに適している
今回のような縦方向への一括代入では、必ず「ReDim 配列(行数 – 1, 0)」のように、列番号を含めた2次元の構造で定義してください。
4. サンプルプログラム
以下のコードは、A列の1行目から100行目まで一括で値を代入する実用的なサンプルです。
Sub BatchInsertData()
‘ 配列の宣言(Variant型が必須)
Dim inpData() As Variant
Dim i As Long
Dim rowCount As Long: rowCount = 100
‘ データを格納するための配列をメモリ上に確保
‘ 縦方向(行方向)に代入するため、(行数-1, 0)の2次元配列にする
ReDim inpData(0 To rowCount – 1, 0 To 0)
‘ 配列に値を格納(シートにはまだ書き込まない)
For i = 0 To rowCount – 1
inpData(i, 0) = “データ_” & (i + 1)
Next i
‘ Resizeメソッドで配列のサイズに合わせた範囲を指定し、一括代入
‘ UBound(inpData, 1) + 1 で配列の行数を取得
Range(“A1”).Resize(UBound(inpData, 1) + 1, 1).Value = inpData
MsgBox “100件のデータを一括挿入しました!”
End Sub
5. 応用・注意点:現場で陥りやすい罠
・Variant型を使用する:配列をセルに代入する場合、配列変数は必ずVariant型で宣言してください。他の型(String型など)で宣言すると、一括代入の際にエラーが発生します。
・配列のインデックス:ReDimで要素数を指定する際、0から始まることを忘れないでください。「100個」確保したい場合は「ReDim 配列(99, 0)」となります。
・範囲の不一致:代入先の「Resize」で指定するサイズと、配列の要素数が一致していないと、データが正しく反映されなかったり、余計な空白セルが混ざったりします。必ず「UBound関数」を使用して、配列のサイズを動的に取得するようにしましょう。
このテクニックを習得すれば、大量のデータを扱う集計業務が驚くほどスムーズになります。ぜひ明日の業務から取り入れてみてください。
