【VBA】Dictionary（辞書）の使い道が一発で分かる記事｜ループ内SQLをやめて高速化する実務テク

ループ内SQLをやめて高速化する実務テク

ループの中でSQLを毎回発行していると、DBとの往復が行数分発生して、処理がかなり遅くなりやすいということ。

Dictionaryって何？（一言で）

VBAの Dictionary（辞書） は、
「キー（検索に使うID） → 値（取り出したいデータ）」を保存して、キーで一発検索できる仕組みです。

配列だと「何番目？」を探す。
Dictionaryだと「商品コード“12345”の情報ちょうだい」で終わる。

---

Dictionaryの強い使い道（実務でよくある3つ）

1) コード変換（商品コード→商品名など）

マスタを辞書化して、明細の変換を高速化。

2) 重複集計（同じキーをまとめる）

商品コード別の数量集計、得意先別の件数集計など。

3) ループ内SQLをやめて高速化（本命）

明細1行ごとにSQL発行してると、DB往復が発生して激遅になります。
そこで、ループの前でSQLを1回だけ実行し、結果をDictionaryに全部入れておく。
あとはループ中はDictionary検索だけにすれば、桁違いに速くなります。

---

まずは超基本：Dictionaryの最小セット

実務でまず使うのはこの4つだけでOK。

• dic(key) = value：追加/更新（実務はこれが一番安全）
• dic.Exists(key)：存在チェック
• dic(key)：値の取得
• dic.Count：件数

---

使い道の例①：商品コード別に数量を集計（重複をまとめる）

（A列：商品コード / B列：数量）

Option Explicit

Sub SUB_SAMPLE_SUM_BY_CODE()

    Dim l_objDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String
    Dim l_LngQty As Long

    Set l_objDic = CreateObject("Scripting.Dictionary")

    With ActiveSheet
        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow
            l_StrCode = CStr(.Cells(l_LngRow, "A").Value)
            l_LngQty = CLng(.Cells(l_LngRow, "B").Value)

            If l_objDic.Exists(l_StrCode) Then
                l_objDic(l_StrCode) = CLng(l_objDic(l_StrCode)) + l_LngQty
            Else
                l_objDic(l_StrCode) = l_LngQty
            End If
        Next
    End With

    Dim l_VarKey As Variant
    For Each l_VarKey In l_objDic.Keys
        Debug.Print l_VarKey & " = " & l_objDic(l_VarKey)
    Next

End Sub

---

使い道の例②：マスタ変換（コード→名称）を高速化

商品マスタを辞書にして、明細ループで引くだけ。

Option Explicit

Private Function FNC_GET_SHOUHIN_DIC(ByVal p_ObjMstSheet As Worksheet) As Object

    Dim l_objDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String
    Dim l_StrName As String

    Set l_objDic = CreateObject("Scripting.Dictionary")

    With p_ObjMstSheet
        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow
            l_StrCode = CStr(.Cells(l_LngRow, "A").Value) ' 商品コード
            l_StrName = CStr(.Cells(l_LngRow, "B").Value) ' 商品名
            l_objDic(l_StrCode) = l_StrName              ' 追加/更新
        Next
    End With

    Set FNC_GET_SHOUHIN_DIC = l_objDic

End Function

---

使い道の例③（本命）：ループ内SQLをやめてDictionaryで高速化する

ここが一番「効く」使い方。

NG例：ループの中でSQLを毎回発行（遅い）

明細が1000行なら、SQLも1000回。
DB往復が一番遅いので、これがボトルネックになりがち。

' ※概念例（この構造が遅い）
For l_LngRow = 2 To l_LngLastRow

    l_StrCode = CStr(.Cells(l_LngRow, "A").Value)

    ' ここで毎回SQL → 遅い（往復が積み上がる）
    l_StrSql = "SELECT 商品名 FROM 商品マスタ WHERE 商品コード = '" & l_StrCode & "'"
    ' Recordset.Open l_StrSql, ...
    ' .Cells(l_LngRow, "B").Value = rs!商品名

Next

※文字列連結でSQLを作る方法は、条件値の扱いによってはセキュリティ面でも注意が必要です。ここではDictionaryによる高速化の説明に絞っています。

OK例：ループの前にSQL結果をDictionaryへ読み込む（速い）

やり方はシンプル：

• ループ前に SQLを1回だけ発行
• 取得結果を dic(商品コード)=商品名 の形で全部入れる
• ループ中は dic(code) を引くだけ（DBアクセスしない）

① SQL結果をDictionary化する関数（ADO想定）

※ADO参照設定なしでも動きやすいように、CreateObjectで書いています。実務では環境に合わせて接続方法を調整してください。

Option Explicit

Private Function FNC_GET_SHOUHIN_FROM_DB_DIC(ByVal p_ObjCn As Object) As Object
On Error GoTo ERROR_HANDLER

    Dim l_objDic As Object
    Dim l_ObjRs As Object
    Dim l_StrSql As String
    Dim l_StrCode As String
    Dim l_StrName As String

    Set l_objDic = CreateObject("Scripting.Dictionary")

    ' 例：必要なマスタを一括取得（条件があるなら WHERE で絞る）
    l_StrSql = ""
    l_StrSql = l_StrSql & "SELECT 商品コード, 商品名 "
    l_StrSql = l_StrSql & "FROM 商品マスタ "

    Set l_ObjRs = CreateObject("ADODB.Recordset")
    l_ObjRs.Open l_StrSql, p_ObjCn

    Do While l_ObjRs.EOF = False
        l_StrCode = Format$(CLng(l_ObjRs.Fields(0).Value), "00000")
        If IsNull(l_ObjRs.Fields(1).Value) Then
            l_StrName = ""
        Else
            l_StrName = CStr(l_ObjRs.Fields(1).Value)
        End If

        l_objDic(l_StrCode) = l_StrName ' 追加/更新

        l_ObjRs.MoveNext
    Loop

    l_ObjRs.Close
    Set l_ObjRs = Nothing

    Set FNC_GET_SHOUHIN_FROM_DB_DIC = l_objDic
    Exit Function

ERROR_HANDLER:
    If Not l_ObjRs Is Nothing Then
        If l_ObjRs.State <> 0 Then l_ObjRs.Close
        Set l_ObjRs = Nothing
    End If

    Set FNC_GET_SHOUHIN_FROM_DB_DIC = Nothing
End Function

② 明細ループはDictionary検索だけ

Option Explicit

Sub SUB_SAMPLE_FAST_LOOKUP()

    On Error GoTo ERROR_HANDLER

    Dim l_ObjCn As Object
    Dim l_ObjDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String

    ' DB接続は環境に合わせて設定する
    Set l_ObjCn = CreateObject("ADODB.Connection")

    l_ObjCn.ConnectionString = "接続文字列を設定"
    l_ObjCn.Open

    ' ★ ここがポイント：ループ前に辞書を作る
    Set l_ObjDic = FNC_GET_SHOUHIN_FROM_DB_DIC(l_ObjCn)

    If l_ObjDic Is Nothing Then
        MsgBox "商品マスタの取得に失敗しました。", vbExclamation
        GoTo EXIT_PROC
    End If

    With ThisWorkbook.Worksheets("明細")

        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow

            l_StrCode = Format$(CLng(.Cells(l_LngRow, "A").Value), "00000")

            If l_ObjDic.Exists(l_StrCode) Then
                .Cells(l_LngRow, "B").Value = CStr(l_ObjDic(l_StrCode))
            Else
                .Cells(l_LngRow, "B").Value = "（未登録）"
            End If

        Next

    End With

EXIT_PROC:

    If Not l_ObjCn Is Nothing Then
        If l_ObjCn.State <> 0 Then
            l_ObjCn.Close
        End If
    End If

    Set l_ObjDic = Nothing
    Set l_ObjCn = Nothing

    Exit Sub

ERROR_HANDLER:

    MsgBox "エラーが発生しました。" & vbCrLf & _
           "エラー番号：" & Err.Number & vbCrLf & _
           "内容：" & Err.Description, _
           vbExclamation

    Resume EXIT_PROC

End Sub

---

なぜ速くなるの？（理由はDBの往復）

• ループ内SQL：行数分、DB往復
• Dictionary方式：DB往復は1回（もしくは少数回）＋ ループはメモリ参照だけ
• メモリ参照（Dictionary検索）は、DB往復より圧倒的に軽いので、行数が増えるほど差が出ます。

※ただし、マスタが巨大で明細件数が少ない場合は、明細側の商品コードを重複排除したうえで、IN句、JOIN、作業テーブルなどを使用し、必要なデータだけを1回または少数回のSQLで取得すると省メモリです。原則として、明細1行ごとのSQL発行は避けます。Dictionary化する範囲は、「明細件数」と「マスタ件数」のバランスを見て判断してください。

---

落とし穴（実務で事故りやすいポイント）

1) キーは型ブレさせない（基本は文字列）

Excel側とDB側で、00123と123のように形式が異なると、同じコードでもDictionaryのキーが一致しません。
商品コードが5桁固定の場合は、Dictionaryへ登録する側と検索する側の両方を、同じ5桁形式に統一します。

l_StrCode = Format$(CLng(value), “00000”)

2) 取得件数が多すぎる場合はSQLで絞る

全件マスタが巨大なら、必要な範囲だけ取得します。WHEREで対象データを絞り、SELECTする列も必要なものだけにします。必要に応じてJOINも使用します。

3) 「存在しない」ケースを必ず処理

未登録コードは現場で普通に出るので、Exists は実務では必須。

---

まとめ：Dictionaryは「検索のためのメモリDB」

• 配列より読みやすい
• コード変換・集計に強い
• ループ内SQLを減らせる場面では、体感で別世界に速くなることがあります

免責事項

本記事のサンプルプログラムは、学習・参考用として掲載しているもので、動作や結果を保証するものではありません。 利用する場合は、ご自身の環境に合わせて確認しながらお使いください。万が一トラブルや損害が発生した場合でも、当サイトでは責任を負いかねます。