配列の基本（Range.Value で一括読み込み・書き戻し、UBound と ReDim Preserve の注意）

どんな場面で使うか

数千〜数万行の明細に対して「区分を判定して列に書き込む」「条件に合う行だけを別シートへ書き出す」といった処理をすると、セルを1つずつ読み書きするマクロでは数分かかることがあります。VBA からセルへ1回アクセスするたびに Excel 側の処理が発生するためです。

セル範囲の値を配列にまとめて読み込み、計算はすべて配列の中で行い、最後に一度だけセルへ書き戻すと、セルへのアクセス回数が大幅に減り、処理時間を短くできます。この記事では、その基本の型と、配列で間違えやすい点を説明します。

Range.Value で読み込んだ配列の形

Variant 型の変数にセル範囲の Value を代入すると、「行, 列」の二次元配列になります。添字は 0 ではなく 1 から始まり、1つ目の添字が行、2つ目の添字が列です。行数は UBound(配列, 1)、列数は UBound(配列, 2) で取得できます。

ただし、範囲が1セルだけのときは配列にならず、単一の値が入ります。1列だけを読み込む処理でデータが1件しかない場合などに起こるため注意が必要です。また、見出し行の下にデータが1行もないと読み込む範囲が見出しにかかってしまうため、事前に行数を確認しておくと安全です。

一括で読み込み、判定して、一括で書き戻す

次のコードは、E列の金額に応じて F列に「ランク」を付ける例です。A〜E列を読み込み、結果は1列分の配列に入れてから F列にまとめて書き込みます。読み込んだ範囲全体を書き戻すと、ほかの列にある数式が値に置き換わってしまうため、書き込むのは結果の列だけにしています。

Sub SetRank()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim src As Variant, result() As Variant

    Set ws = ThisWorkbook.Worksheets("明細")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "データがありません"                  ' 見出しだけのときは処理しない
        Exit Sub
    End If

    src = ws.Range("A2:E" & lastRow).Value          ' 一括読み込み（見出しは除く）
    ReDim result(1 To UBound(src, 1), 1 To 1)       ' 書き戻し用（行数×1列）

    For i = LBound(src, 1) To UBound(src, 1)
        If IsNumeric(src(i, 5)) And Not IsEmpty(src(i, 5)) Then
            Select Case src(i, 5)
                Case Is >= 1000000: result(i, 1) = "A"
                Case Is >= 300000:  result(i, 1) = "B"
                Case Else:          result(i, 1) = "C"
            End Select
        Else
            result(i, 1) = "要確認"                  ' 数値でない金額は目印を付ける
        End If
    Next i

    ws.Range("F2").Resize(UBound(result, 1), 1).Value = result   ' 一括書き戻し
End Sub

件数が分からない結果を集めるときの ReDim Preserve

条件に合う行だけを集めるときは、結果の件数が事前に分かりません。ReDim Preserve を使うと中身を残したまま配列の大きさを変えられますが、変えられるのは最後の次元だけです。「行, 列」の配列で行を増やそうとすると「インデックスが有効範囲にありません」のエラーになります。

実務では、最大件数（元データの行数）で先に配列を確保し、使った行数だけを書き出す方法が簡単です。ループのたびに ReDim Preserve を繰り返すと、そのたびに配列全体がコピーされて遅くなる点からも、この方法をおすすめします。

Dim outArr() As Variant, hit As Long
ReDim outArr(1 To UBound(src, 1), 1 To 3)   ' 最大件数で確保しておく
hit = 0
For i = 1 To UBound(src, 1)
    If src(i, 3) = "東京" Then
        hit = hit + 1
        outArr(hit, 1) = src(i, 1)
        outArr(hit, 2) = src(i, 2)
        outArr(hit, 3) = src(i, 5)
    End If
Next i
If hit > 0 Then wsOut.Range("A2").Resize(hit, 3).Value = outArr   ' 使った行数だけ書く

配列で間違えやすい点

Array 関数や Split 関数で作った配列は 0 から始まり、Range.Value の配列は 1 から始まります。混在させると1つずれた値を参照してしまうため、ループは決め打ちの数字ではなく LBound と UBound で書く習慣を付けると安心です。

また、書き戻すときは Resize で貼り付け先の大きさを配列とそろえます。範囲が配列より大きいと余った部分に「#N/A」が表示され、小さいとはみ出した分が書き込まれません。日付や「001」のようなコードは、書き戻すときに Excel が自動で変換することがあるため、必要に応じて事前に書き込み先の表示形式を文字列にしておきます。

• Range.Value の配列は 1 始まり、Split や Array は 0 始まり
• ReDim Preserve で変えられるのは最後の次元だけ
• 書き戻し先は Resize で配列と同じ大きさにする

ほかの記事