【Excel VBA】2次元配列の値をセル範囲に一括で書き込んで高速化する方法

2025年8月13日

VBAマクロで計算・加工した大量の処理結果を、最終的にワークシートに書き出す場面は非常に多くあります。このとき、Forループを使って1セルずつ値を書き込んでいくと、データ量が増えるにつれて処理が極端に遅くなってしまいます。

前回の「セル範囲→配列」の記事とは逆に、今回はVBAの2次元配列に格納されたデータを、指定したセル範囲に一瞬で書き込むための、最も高速で効率的な方法を解説します。

核心は .Resize プロパティ

このテクニックの鍵となるのが、Rangeオブジェクトの.Resizeプロパティです。 .Resizeは、基準となるセル範囲を、指定した行数・列数の大きさにリサイズ（拡張・縮小）する機能を持っています。

基準セル.Resize(行数, 列数)

例えば、Range("A1").Resize(10, 5)は、A1セルを基準に「10行 × 5列」の大きさの範囲、つまりA1:E10のセル範囲を返します。

この.Resizeと、配列の大きさを取得するUBound関数を組み合わせることで、書き込み先のセル範囲の大きさを、配列のサイズと完全に一致させることができます。

完成したVBAコード

以下は、まずVBA内で1000行×5列の2次元配列を作成し、その全データをA1セルを起点として一括でシートに書き出すコードです。

Sub WriteArrayToRange()

    ' 変数を宣言します
    Dim dataArray(1 To 1000, 1 To 5) As String
    Dim r As Long
    Dim c As Long
    Dim outputCell As Range
    
    '--- 1. VBAのメモリ上で2次元配列にデータを作成 ---
    ' (実際には、他の処理の結果として配列が作られることを想定)
    For r = 1 To UBound(dataArray, 1)
        For c = 1 To UBound(dataArray, 2)
            dataArray(r, c) = "Row" & r & ", Col" & c
        Next c
    Next r
    
    '--- 2. 書き出し先の起点となるセルを設定 ---
    Set outputCell = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    '--- 3. 配列の大きさに合わせてセル範囲をリサイズし、一括で値を代入 ---
    ' この一行で、すべてのデータが高速に書き込まれます
    outputCell.Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
    
    MsgBox "配列データをシートに一括で書き込みました。"

End Sub

このコードを実行すると、1000行×5列（合計5000セル）のデータが、ループで1セルずつ書き込むのとは比較にならない速さで、一瞬でシートに反映されます。

コードのポイント解説

① 配列の準備

Dim dataArray(1 To 1000, 1 To 5) As String

まず、VBAのメモリ上に、シートに書き出したいデータが格納された2次元配列を用意します。配列のインデックスは、Range.Valueから読み込んだ場合と同様に、1から始まるように定義するのが一般的です。

② 配列の大きさにリサイズして一括代入

outputCell.Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray

この一行が、高速化を実現する核心部分です。

UBound(dataArray, 1): 配列の1次元目の要素数、つまり行数を取得します。
UBound(dataArray, 2): 配列の2次元目の要素数、つまり列数を取得します。
outputCell.Resize(...): outputCell（A1セル）を起点に、配列と全く同じ行数・列数のセル範囲を動的に作り出します。
.Value = dataArray: 作成されたセル範囲の.Valueプロパティに、配列変数を直接代入します。これにより、VBAとExcel間の通信が一度で済み、すべてのデータが高速に転送されます。