Excel VBA 逆引き集 | 配列×ループ

Excel VBA
スポンサーリンク

配列×ループ

配列に読み込んでからループで処理すると、セルへ何度もアクセスするより圧倒的に速く・安全に動きます。ここでは、初心者がつまずきやすい宣言・代入・取り出しから、2次元配列（Range.Value）、高速一括書き戻しまでを例題付きで解説します。

基本：1次元配列とループ

Sub Array1D_Basic()
    Dim names(0 To 2) As String  '固定長配列（3要素）
    names(0) = "田中"
    names(1) = "佐藤"
    names(2) = "鈴木"

    Dim i As Long
    For i = LBound(names) To UBound(names)
        Cells(i + 2, "A").Value = names(i)
    Next i
End Sub
VB
  • ポイント:
    • LBound/UBound: 配列の下限・上限を取得して安全に回す。
    • 固定長配列: 要素数が決まっているときに簡単。

可変長配列（ReDim）と拡張

Sub Array1D_Dynamic()
    Dim arr() As Double         '可変長
    ReDim arr(1 To 5)           '必要になったらサイズ確保

    Dim i As Long
    For i = 1 To 5
        arr(i) = i * 10
    Next i

    'サイズを増やす（中身保持）
    ReDim Preserve arr(1 To 8)
    arr(6) = 999

    For i = LBound(arr) To UBound(arr)
        Cells(i + 1, "B").Value = arr(i)
    Next i
End Sub
VB
  • ポイント:
    • ReDim: 実行時にサイズ変更。
    • Preserve: 既存データを保持。最終次元のみ拡張可能。

2次元配列：Range.Valueを読み込む（最速の定番）

Sub Array2D_FromRange()
    Dim rg As Range
    Set rg = Range("C2:F1000")

    Dim data As Variant
    data = rg.Value  '2次元配列（1-based）へ

    Dim r As Long, c As Long
    For r = 1 To UBound(data, 1)
        For c = 1 To UBound(data, 2)
            If IsNumeric(data(r, c)) Then
                data(r, c) = data(r, c) * 1.1  '例：10%上乗せ
            End If
        Next c
    Next r

    rg.Value = data  '一括で書き戻し
End Sub
VB
  • ポイント:
    • Variant配列: Range.Valueは2次元Variant配列で受けるのが基本。
    • 一括書き戻し: セルへの個別アクセスを避けて高速化。

For Eachで配列を回す（Variant配列）

Sub Array_ForEach()
    Dim arr As Variant
    arr = Array("A", "B", "C")  '1次元のVariant配列

    Dim v As Variant, i As Long
    i = 2
    For Each v In arr
        Cells(i, "D").Value = v
        i = i + 1
    Next v
End Sub
VB
  • ポイント:
    • Array関数: 手早く配列を作れる（Variant配列）。
    • For Each: 要素を直感的に処理。添字が不要。

実務テンプレート：配列×ループの王道

1) 大量データを配列で読み込み→計算→一括書き戻し

'1) 大量データを配列で読み込み→計算→一括書き戻し
Sub Template_ArrayBatchCalc()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim rg As Range: Set rg = Range("A2:D50000")
    Dim v As Variant: v = rg.Value

    Dim r As Long
    For r = 1 To UBound(v, 1)
        '数量=C（3列目）, 単価=D（4列目）→金額をB（2列目）に書く例
        If IsNumeric(v(r, 3)) And IsNumeric(v(r, 4)) Then
            v(r, 2) = v(r, 3) * v(r, 4)
        End If
    Next r

    rg.Value = v

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
VB

2) 片方向の整形処理（Trimや大文字化を配列に適用）

'2) 片方向の整形処理（Trimや大文字化を配列に適用）
Sub Template_CleanText()
    Dim rg As Range: Set rg = Range("B2:B10000")
    Dim v As Variant: v = rg.Value

    Dim r As Long
    For r = 1 To UBound(v, 1)
        If Not IsEmpty(v(r, 1)) Then
            v(r, 1) = UCase$(Trim$(CStr(v(r, 1))))
        End If
    Next r

    rg.Value = v
End Sub
VB
  • ポイント:
    • 速度ラップ: 画面更新と再計算OFF→ONで体感速度UP。
    • 型変換: 文字列処理は CStr、数値は IsNumericチェックが安全。

行列ループ：2次元配列で条件塗り分け

Sub Array_ColorByThreshold()
    Dim rg As Range: Set rg = Range("E2:H2000")
    Dim v As Variant: v = rg.Value

    Dim r As Long, c As Long
    For r = 1 To UBound(v, 1)
        For c = 1 To UBound(v, 2)
            If IsNumeric(v(r, c)) Then
                If v(r, c) >= 80 Then
                    '値側ではなく後でセルへ反映する指示列を使う例
                    v(r, c) = v(r, c)  '値はそのまま
                End If
            End If
        Next c
    Next r

    rg.Value = v

    '色はセルに対して適用（書式は一括代入できないため直接セルへ）
    Dim r2 As Long, c2 As Long
    For r2 = 1 To rg.Rows.Count
        For c2 = 1 To rg.Columns.Count
            If IsNumeric(rg.Cells(r2, c2).Value) Then
                If rg.Cells(r2, c2).Value >= 80 Then
                    rg.Cells(r2, c2).Interior.Color = RGB(198, 239, 206)
                Else
                    rg.Cells(r2, c2).Interior.Color = RGB(255, 199, 206)
                End If
            End If
        Next c2
    Next r2
End Sub
VB
  • ポイント:
    • 値は配列で、書式はセルへ: 値の計算は配列で最速、書式はセル操作が必要。

例題で練習

例題1：CSV風データをSplitで配列化→行へ書き出し

Sub Example_SplitCsvRow()
    Dim line As String
    line = "東京,営業A,120,800"

    Dim parts As Variant
    parts = Split(line, ",")

    Dim i As Long
    For i = LBound(parts) To UBound(parts)
        Cells(2, i + 1).Value = parts(i)
    Next i
End Sub
VB

例題2：選択範囲を配列化して「10%値引き」

Sub Example_DiscountSelection()
    If TypeName(Selection) = "Range" Then
        Dim v As Variant
        v = Selection.Value
        Dim r As Long, c As Long
        For r = 1 To UBound(v, 1)
            For c = 1 To UBound(v, 2)
                If IsNumeric(v(r, c)) Then v(r, c) = v(r, c) * 0.9
            End If
        Next r
        Selection.Value = v
    End If
End Sub
VB

例題3：2列を配列で読み込んで結合文字列を作る

Sub Example_ConcatTwoCols()
    Dim rg As Range: Set rg = Range("A2:B1000")
    Dim v As Variant: v = rg.Value

    Dim out() As Variant
    ReDim out(1 To UBound(v, 1), 1 To 1)

    Dim r As Long
    For r = 1 To UBound(v, 1)
        out(r, 1) = Trim$(CStr(v(r, 1))) & " - " & Trim$(CStr(v(r, 2)))
    Next r

    Range("C2").Resize(UBound(out, 1), 1).Value = out
End Sub
VB

実務の落とし穴と対策

  • 配列のインデックス起点: Range.Valueの2次元配列は 1 始まり。Array()や固定長の多くは 0 始まり。LBound/UBoundで境界を必ず取る。
  • 型の混在: 数値と文字列が混ざると計算時にエラーになりやすい。IsNumeric/CStr/Val を適切に使う。
  • Preserveの制約: ReDim Preserveは最終次元しか伸縮できない。構造変更が必要なら新配列へコピー。
  • 書式操作のコスト: 背景色・フォントなどはセルアクセスが必要。値計算は配列、一括色付けは対象範囲を特定して最小限に。
  • 速度最適化の基本: 配列で一括読み書き、ScreenUpdating/CalculationのOFF→ON、Select/Activateを使わない。
Sub SpeedWrap_Array()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    '…配列×ループの本処理…

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
VB

スポンサーリンク
タイトルとURLをコピーしました