VBAの配列とDictionaryの使い方｜セル一括読み書きと重複削除・集計

VBAの配列は「順番に並んだ表をまとめて持つ入れ物」、Dictionaryは「キーを指定して値を出し入れする入れ物（連想配列）」です。 セルの読み書きは配列で一度に済ませ、重複を除く・キーごとに数える・合計する処理はDictionaryに任せる、という組み合わせが基本の形になります。

この記事では、使い分けの早見表、セル範囲の一括読み込みから集計・一括書き戻しまでを1本にまとめた通しコード、メソッドとプロパティの一覧、エラー番号別の対処表を載せます。コードで使うメソッド・プロパティは、Microsoft Learn の VBA リファレンスで構文と引数を1つずつ確認したものだけにしています。

確認環境：Microsoft Learn の Office VBA リファレンス（英語版ページ）を 2026-10-05 に開いて、構文・引数・エラーの記載を照合しました。サンプルデータの集計値は、同じ集計を Python 3.11 で計算した値です。

この記事の結論

• 順番に並んだ表をそのまま扱うなら配列。セル範囲は Range.Value で2次元配列として一度に読み、加工してから一度に書き戻します。
• キーで探す・重複を除く・キーごとに数える／合計するなら Dictionary。Exists で「もう登録済みか」をすぐ判定できます。
• 配列の添字は数字で決め打ちせず、LBound〜UBound で回す。下限が 0 の配列と 1 の配列が1つのコードに混ざるためです。
• Dictionary の Add は同じキーで2回呼ぶとエラーになります。Exists で分岐するか、Item への代入で上書きします。
• Dictionary は VBA 本体ではなく Scripting ライブラリのオブジェクトです。VBA 本体だけで書きたいブックは配列と Collection でまとめます。

配列・Collection・Dictionaryの早見表

どれを使うかの判断表

配列の基本：添字・LBound・UBound・ReDim Preserve

添字の下限は「宣言のしかた」で決まる

配列の添字（番号）の下限は、宣言の書き方によって変わります。Microsoft のリファレンスでは、下限の既定値は 0 で、Option Base 1 をモジュールの先頭に書くと 1 になり、To を使えば任意の整数にできると説明されています。

Dim a(3)            ' 下限は Option Base の設定に従う（既定は 0。0〜3 の4要素）
Dim b(1 To 3)       ' To で明示。1〜3 の3要素
Dim c(1 To 5, 1 To 2)  ' 2次元。5行×2列

このように下限がそろわないので、ループは LBound(配列) から UBound(配列) までと書くのが安全です。2次元配列では UBound(配列, 1) が行方向、UBound(配列, 2) が列方向の上限です。

Dim i As Long
For i = LBound(b) To UBound(b)
    Debug.Print i, b(i)
Next i

ReDim Preserveで中身を残したまま広げる

要素数があとから決まる配列は、Dim a() と大きさを書かずに宣言し、ReDim で大きさを決めます。Preserve を付けると、入っている値を残したまま大きさを変えられます。

Dim a() As Long
ReDim a(1 To 3)
a(1) = 10: a(2) = 20: a(3) = 30
ReDim Preserve a(1 To 5)   ' 1〜3 の値は残り、4・5 が増える

Preserve には、リファレンスに明記された3つの決まりがあります。

行を増やしていきたい2次元配列は、この決まりのため ReDim Preserve では広げられません。行数が先に分からないときは、Dictionary や Collection に貯めてから、件数が確定した時点で ReDim 出力(1 To 件数, 1 To 列数) と一度で確保すると、迷いが少なくなります。後半の通しコードはこの形です。

セル範囲を配列に一括で読み書きする

Range.Value は、2つ以上のセルを含む範囲に対して、セルの値が入った2次元配列を Variant で返します。逆に、2次元配列を Value に代入すると、値が1回の操作で範囲に写されます。セルを1つずつ読み書きする回数が減るので、行数が多い表ほど処理が軽くなります。

Sub DoubleQuantity()
    Dim rng As Range
    Dim v As Variant
    Dim i As Long

    Set rng = ThisWorkbook.Worksheets("申請").Range("C2:C13")
    v = rng.Value                       ' 一括で読む（1列でも2次元配列）

    For i = LBound(v, 1) To UBound(v, 1)
        v(i, 1) = v(i, 1) * 2           ' 1列だけでも (行, 1) と2つの添字で指定する
    Next i

    rng.Value = v                       ' 一括で書き戻す
End Sub

覚えておきたい点は3つです。

• 1列だけ・1行だけ読んでも2次元配列です。v(i) ではなく v(i, 1)（1行だけなら v(1, j)）と書きます。
• 2次元配列が返るのは、範囲が2セル以上のときです。1セルだけの範囲では配列にならないので、IsArray(v) で確かめてから回します。
• 書き込み先の範囲が配列より大きいと、余ったセルにはエラー値が入るとリファレンスにあります。Resize(UBound(v, 1), UBound(v, 2)) で配列と同じ大きさにそろえてから代入します。

Dictionaryの基本：Add・Exists・Item・Keys

Dictionary は、キーと値（リファレンスでは item）の組を持つオブジェクトです。キーには配列以外なら何でも使えますが、文字列か整数にするのが一般的です。

Sub DictionaryBasics()
    Dim d As Object
    Dim k As Variant

    Set d = CreateObject("Scripting.Dictionary")

    d.Add "PC", 3                  ' キーと値を追加
    d.Add "モニター", 5

    If d.Exists("PC") Then         ' キーがあるか
        d.Item("PC") = d.Item("PC") + 1   ' 値を読んで、足して、代入し直す
    End If

    d.Item("マウス") = 2           ' 無いキーへの代入は、新しいキーとして追加される

    For Each k In d.Keys           ' キーを順に取り出す
        Debug.Print k, d.Item(k)
    Next k

    d.Remove "モニター"            ' 1組を取り除く
    Debug.Print d.Count            ' 組の数
End Sub

Add と Item への代入は、同じキーがすでにあるときの動きが違います。 Add はエラーになり、Item(key) = 値 は上書きします。「初めて出てきたキーだけ登録したい」なら Exists で分岐して Add、「最後の値で上書きしてよい」なら Item への代入、と使い分けます。

通しコード：一括で読み、重複削除・件数・合計・複合キー集計をして、一括で書き戻す

備品の貸出申請を記録した表を例にします。シート名は「申請」、A列に部署、B列に品名、C列に数量が入っているとします。

シートの A1 に貼り付けて試せるタブ区切り版です。

部署  品名  数量
総務  ノートPC   2
開発  モニター    3
総務  モニター    1
営業  ノートPC   1
開発  ノートPC   4
開発  モニター    2
総務  ノートPC   1
営業  マウス 5
開発  マウス 2
営業  ノートPC   2
総務  マウス 3
開発  モニター    1

コード全体

標準モジュールにそのまま貼り付けます。E列に「品名の重複を除いた一覧」、G〜I列に「部署ごとの件数と数量合計」、K〜M列に「部署×品名の数量合計」を書き出します。

Option Explicit

Sub SummarizeRequests()
    Dim ws As Worksheet
    Dim src As Variant            ' 読み込んだ表（2次元配列）
    Dim dicItem As Object         ' 品名（重複を除く）
    Dim dicCount As Object        ' 部署 → 件数
    Dim dicSum As Object          ' 部署 → 数量の合計
    Dim dicPair As Object         ' 部署|品名 → 数量の合計
    Dim i As Long
    Dim dept As String, itemName As String, pairKey As String
    Dim qty As Double

    Set ws = ThisWorkbook.Worksheets("申請")

    ' --- 1. セル範囲を配列に一括で読む ---
    src = ws.Range("A1").CurrentRegion.Value
    If Not IsArray(src) Then
        MsgBox "表が見つかりません。A1 から見出しとデータを入れてください。"
        Exit Sub
    End If

    ' --- 2. Dictionary を用意する ---
    Set dicItem = CreateObject("Scripting.Dictionary")
    Set dicCount = CreateObject("Scripting.Dictionary")
    Set dicSum = CreateObject("Scripting.Dictionary")
    Set dicPair = CreateObject("Scripting.Dictionary")
    dicItem.CompareMode = vbTextCompare     ' キーをテキスト比較で比べる
    dicCount.CompareMode = vbTextCompare
    dicSum.CompareMode = vbTextCompare
    dicPair.CompareMode = vbTextCompare

    ' --- 3. 配列を1行ずつ回して Dictionary に貯める ---
    For i = LBound(src, 1) + 1 To UBound(src, 1)     ' +1 で見出し行を飛ばす
        dept = src(i, 1)
        itemName = src(i, 2)
        qty = src(i, 3)

        ' 重複削除：初めて出てきた品名だけ登録する
        If Not dicItem.Exists(itemName) Then
            dicItem.Add itemName, i
        End If

        ' 件数と合計：あれば足す、無ければ新しく登録する
        If dicCount.Exists(dept) Then
            dicCount.Item(dept) = dicCount.Item(dept) + 1
            dicSum.Item(dept) = dicSum.Item(dept) + qty
        Else
            dicCount.Add dept, 1
            dicSum.Add dept, qty
        End If

        ' 複合キー：2つの値を区切り文字でつないで1つのキーにする
        pairKey = dept & "|" & itemName
        If dicPair.Exists(pairKey) Then
            dicPair.Item(pairKey) = dicPair.Item(pairKey) + qty
        Else
            dicPair.Add pairKey, qty
        End If
    Next i

    ' --- 4. 結果を配列にまとめて、一括で書き戻す ---
    ws.Range("E:M").ClearContents
    WriteItems ws.Range("E1"), dicItem
    WriteCountAndSum ws.Range("G1"), dicCount, dicSum
    WritePairs ws.Range("K1"), dicPair
End Sub

' 重複を除いた一覧を1列に書く
Private Sub WriteItems(ByVal topLeft As Range, ByVal dic As Object)
    Dim out() As Variant
    Dim keys As Variant
    Dim r As Long

    ReDim out(1 To dic.Count + 1, 1 To 1)
    out(1, 1) = "品名"
    keys = dic.Keys
    For r = LBound(keys) To UBound(keys)
        out(r - LBound(keys) + 2, 1) = keys(r)
    Next r
    topLeft.Resize(UBound(out, 1), UBound(out, 2)).Value = out
End Sub

' キーごとの件数と合計を3列に書く
Private Sub WriteCountAndSum(ByVal topLeft As Range, _
                             ByVal dicCount As Object, ByVal dicSum As Object)
    Dim out() As Variant
    Dim keys As Variant
    Dim r As Long, n As Long

    ReDim out(1 To dicCount.Count + 1, 1 To 3)
    out(1, 1) = "部署": out(1, 2) = "件数": out(1, 3) = "数量合計"
    keys = dicCount.Keys
    For r = LBound(keys) To UBound(keys)
        n = r - LBound(keys) + 2
        out(n, 1) = keys(r)
        out(n, 2) = dicCount.Item(keys(r))
        out(n, 3) = dicSum.Item(keys(r))
    Next r
    topLeft.Resize(UBound(out, 1), UBound(out, 2)).Value = out
End Sub

' 複合キーを2列に戻して、合計と合わせて3列に書く
Private Sub WritePairs(ByVal topLeft As Range, ByVal dic As Object)
    Dim out() As Variant
    Dim keys As Variant
    Dim parts As Variant
    Dim r As Long, n As Long

    ReDim out(1 To dic.Count + 1, 1 To 3)
    out(1, 1) = "部署": out(1, 2) = "品名": out(1, 3) = "数量合計"
    keys = dic.Keys
    For r = LBound(keys) To UBound(keys)
        n = r - LBound(keys) + 2
        parts = Split(keys(r), "|")          ' 0 始まりの1次元配列
        out(n, 1) = parts(0)
        out(n, 2) = parts(1)
        out(n, 3) = dic.Item(keys(r))
    Next r
    topLeft.Resize(UBound(out, 1), UBound(out, 2)).Value = out
End Sub

各行の役割

このデータでの集計値

同じ集計を Python で計算した値です。VBA の実行画面を写したものではありません。行の並びは Keys が返す順に従うため、決まった順で見せたいときは書き戻したあとにシート側で並べ替えます。

品名の重複を除いた一覧（12行 → 3件）

部署ごとの件数と数量合計

部署×品名の数量合計（8組）

自分の表に合わせて変える場所

Collectionで同じ重複削除を書く（VBA本体だけで完結）

Collection には Exists がありませんが、キーが重複する Add はエラーになるという決まりを使うと、重複を除いた一覧を作れます。エラーをいったん受け流し、登録できたものだけが残る形です。

Sub UniqueByCollection()
    Dim src As Variant
    Dim c As Collection
    Dim out() As Variant
    Dim i As Long

    src = ThisWorkbook.Worksheets("申請").Range("A1").CurrentRegion.Value
    If Not IsArray(src) Then Exit Sub

    Set c = New Collection
    On Error Resume Next                      ' キー重複のエラー 457 を受け流す
    For i = LBound(src, 1) + 1 To UBound(src, 1)
        c.Add src(i, 2), "k" & src(i, 2)      ' 値とキーの両方に品名を入れる
    Next i
    On Error GoTo 0                           ' エラーの受け流しをここで終える

    ReDim out(1 To c.Count, 1 To 1)
    For i = 1 To c.Count                      ' Collection は 1 番目から数える
        out(i, 1) = c.Item(i)
    Next i
    ThisWorkbook.Worksheets("申請").Range("O1").Resize(c.Count, 1).Value = out
End Sub

Collection のキーは文字列で、キーの一覧を取り出すメソッドは無いので、値にも同じ内容を入れておくのがこつです。件数や合計の集計まで行うなら、値を読んで代入し直せる Dictionary のほうが短く書けます。

メソッド・プロパティ一覧

Dictionary

CompareMode に指定できる値は次のとおりです。

配列

Collection

症状別の対処表

エラーメッセージは Microsoft Learn（英語版）の表記です。日本語版の Excel では日本語のメッセージで表示されます。

確かめて分かったこと

Microsoft Learn のページを1つずつ開いて照合した結果、見落としやすかった点です。

1. Item(key) は読むだけでもキーを作ります。 Item プロパティのページに「既存の値を返そうとしてキーが見つからないときは、新しいキーが作られ、値は空のままになる」とあります。有無の判定に Item を使うと、判定のたびに空のキーが増えます。
2. CompareMode は空のうちにしか変えられません。 データが入った Dictionary の比較方法を変えるとエラーになる、と明記されています。
3. Array 関数の下限は Option Base の影響を受けます。 VBA.Array と書いた場合だけ、Option Base に関係なく 0 になります。
4. Range.Value のページには、返ってくる配列の下限の数字が書かれていません。 公式の例は LBound〜UBound で回しているので、この記事のコードも同じ書き方にそろえました。
5. ReDim Preserve で下限を変えるとエラーになります。 「最後の次元だけ」は知られていますが、上限しか動かせない点も同じ段落に書かれています。
6. Collection のメンバーは Add・Item・Remove・Count の4つです。 存在確認やキー一覧のメソッドが無いことが、Dictionary を選ぶ一番の理由になります。

よくある質問

Q. VBAの配列とDictionaryの違いは？ A. 配列は番号（添字）で、Dictionary はキーで値を取り出します。表をそのまま読み書きするなら配列、キーで探す・重複を除く・集計するなら Dictionary が向いています。

Q. DictionaryとCollectionの違いは？ A. Dictionary には存在確認の Exists、キー一覧の Keys、値の代入があり、Collection にはありません。Collection は VBA 本体のオブジェクトなので、別のライブラリを使わずに済みます。

Q. Dictionaryで重複エラーが出るときは？ A. Add の前に Exists で分岐すれば解消します。上書きでよい場合は d.Item(key) = 値 と代入します。

Q. セルの読み書きを速くするには？ A. Range.Value で配列に一度で読み、配列の中で計算して、同じ大きさの範囲に一度で書き戻します。セルに触れる回数が、行数分から2回に減ります。

Q. 参照設定は必要ですか？ A. この記事のコードは CreateObject("Scripting.Dictionary") で作り、変数を As Object で宣言しているので、参照設定をしなくても書けます。

まとめ

• 順番に並んだ表は配列、キーで探す・重複を除く・集計するなら Dictionary。
• セルは Range.Value で一括で読み、同じ大きさに Resize して一括で書き戻す。
• 添字は LBound〜UBound。1列だけ読んでも2次元配列、1セルだけでは配列にならない。
• Add は重複でエラー、Item への代入は上書き。有無の判定は必ず Exists。
• CompareMode は最初の Add より前に設定する。
• 行数が分からない出力は、Dictionary に貯めて件数が決まってから ReDim で一度に確保する。
• VBA 本体だけで書きたいときは配列と Collection で同じ重複削除ができる。

参考・出典

• Microsoft Learn「Dictionary object」「Add method (Dictionary)」「Exists method」「Keys method」「Items method」「Remove method (Dictionary object)」「Item property (Dictionary object)」「Key property」「CompareMode property」 https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object
• Microsoft Learn「Collection object」「Add method (Visual Basic for Applications)」 https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object
• Microsoft Learn「LBound function」「UBound function」「ReDim statement」「Option Base statement」「Array function」「Split function」「IsArray function」 https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/redim-statement
• Microsoft Learn「Range.Value property (Excel)」「Range.Resize property (Excel)」「Range.CurrentRegion property (Excel)」「Range.ClearContents method (Excel)」 https://learn.microsoft.com/en-us/office/vba/api/excel.range.value
• Microsoft Learn「Trappable errors」「Subscript out of range (Error 9)」「This key is already associated with an element of this collection (Error 457)」 https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/trappable-errors
• Microsoft Learn「Office for Mac」 https://learn.microsoft.com/en-us/office/vba/api/overview/office-mac

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。