Excel VBA 逆引き集 | マスタが外部ファイル

Excel VBA
スポンサーリンク

マスタが外部ファイル

「商品マスタや社員マスタが別ブックに保存されていて、明細ブックから参照したい」——そんな場面で役立つのが 外部ファイルを開いて辞書化→JOIN のテンプレです。初心者でも壊れないように、最短のVLOOKUP版、堅牢な辞書版、見出し名対応版を用意しました。

基本の考え方

  • 外部ファイルを開く: Workbooks.Open で読み取り専用で開く
  • マスタ範囲を配列に読み込む: Range("A1").CurrentRegion.Value
  • 辞書に格納: キー→レコードを作る
  • JOIN: 明細に付与して一括書き戻し
  • 最後に閉じる: wb.Close SaveChanges:=False

最短テンプレ：外部ファイルを開いてVLOOKUP

Sub JoinMaster_VLookup_External()
    '明細: このブックのSheet("明細") A=コード, B=数量
    '外部マスタ: "C:\Data\商品マスタ.xlsx" のSheet("マスタ") A=コード, B=名称, C=単価

    Dim wsD As Worksheet: Set wsD = Worksheets("明細")
    Dim lastD As Long: lastD = wsD.Cells(wsD.Rows.Count, "A").End(xlUp).Row

    '外部ファイルを開く（読み取り専用）
    Dim wbM As Workbook: Set wbM = Workbooks.Open("C:\Data\商品マスタ.xlsx", ReadOnly:=True)
    Dim wsM As Worksheet: Set wsM = wbM.Worksheets("マスタ")
    Dim rgM As Range: Set rgM = wsM.Range("A1").CurrentRegion

    '名称・単価をVLOOKUPで付与
    wsD.Range("C1:D1").Value = Array("名称", "単価")
    With wsD.Range("C2:C" & lastD)
        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,'" & wbM.Name & "'!" & rgM.Address(True, True, xlA1, True) & ",2,FALSE),""#N/A"")"
        .Value = .Value
    End With
    With wsD.Range("D2:D" & lastD)
        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,'" & wbM.Name & "'!" & rgM.Address(True, True, xlA1, True) & ",3,FALSE),0)"
        .Value = .Value
    End With

    '外部ファイルを閉じる
    wbM.Close SaveChanges:=False
End Sub
VB
  • ポイント
    • 外部ファイルを開いて範囲を参照。
    • 式は値化して軽く運用。
    • 閉じるときは SaveChanges:=False で安全。

爆速テンプレ：外部ファイルを辞書化してJOIN

Sub JoinMaster_Dictionary_External()
    '明細: このブックのSheet("明細") A=コード, B=数量
    '外部マスタ: "C:\Data\商品マスタ.xlsx" のSheet("マスタ") A=コード, B=名称, C=単価

    Dim wsD As Worksheet: Set wsD = Worksheets("明細")
    Dim rgD As Range: Set rgD = wsD.Range("A1").CurrentRegion
    Dim vD As Variant: vD = rgD.Value

    '外部ファイルを開く
    Dim wbM As Workbook: Set wbM = Workbooks.Open("C:\Data\商品マスタ.xlsx", ReadOnly:=True)
    Dim wsM As Worksheet: Set wsM = wbM.Worksheets("マスタ")
    Dim vM As Variant: vM = wsM.Range("A1").CurrentRegion.Value

    '辞書作成（コード→(名称,単価)）
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long, key As String
    For i = 2 To UBound(vM, 1)
        key = UCase$(Trim$(CStr(vM(i, 1))))
        dict(key) = Array(CStr(vM(i, 2)), Val(vM(i, 3)))
    Next

    '出力配列（明細＋名称＋単価）
    Dim out() As Variant: ReDim out(1 To UBound(vD, 1), 1 To UBound(vD, 2) + 2)
    Dim c As Long: For c = 1 To UBound(vD, 2): out(1, c) = vD(1, c): Next
    out(1, UBound(vD, 2) + 1) = "名称": out(1, UBound(vD, 2) + 2) = "単価"

    Dim r As Long
    For r = 2 To UBound(vD, 1)
        For c = 1 To UBound(vD, 2): out(r, c) = vD(r, c): Next
        key = UCase$(Trim$(CStr(vD(r, 1))))
        If dict.Exists(key) Then
            out(r, UBound(vD, 2) + 1) = dict(key)(0)
            out(r, UBound(vD, 2) + 2) = dict(key)(1)
        Else
            out(r, UBound(vD, 2) + 1) = "#N/A"
            out(r, UBound(vD, 2) + 2) = 0
        End If
    Next

    rgD.Resize(UBound(out, 1), UBound(out, 2)).Value = out

    '外部ファイルを閉じる
    wbM.Close SaveChanges:=False
End Sub
VB
  • ポイント
    • 外部マスタを配列→辞書化してJOIN。
    • セル往復ゼロで高速。
    • 未一致は「#N/A」や0で補完。

見出し名で安全にJOIN（列順変更に強い）

Sub JoinMaster_ByHeaders_External()
    '明細: このブックのSheet("明細") A=コード, B=数量
    '外部マスタ: "C:\Data\商品マスタ.xlsx" のSheet("マスタ") A=コード, B=名称, C=単価

    Dim wsD As Worksheet: Set wsD = Worksheets("明細")
    Dim rgD As Range: Set rgD = wsD.Range("A1").CurrentRegion
    Dim vD As Variant: vD = rgD.Value

    '外部ファイルを開く
    Dim wbM As Workbook: Set wbM = Workbooks.Open("C:\Data\商品マスタ.xlsx", ReadOnly:=True)
    Dim wsM As Worksheet: Set wsM = wbM.Worksheets("マスタ")
    Dim rgM As Range: Set rgM = wsM.Range("A1").CurrentRegion
    Dim vM As Variant: vM = rgM.Value

    '見出し列特定
    Dim cKeyM As Long: cKeyM = FindHeader(rgM.Rows(1), "コード")
    Dim cNameM As Long: cNameM = FindHeader(rgM.Rows(1), "名称")
    Dim cPriceM As Long: cPriceM = FindHeader(rgM.Rows(1), "単価")

    '辞書作成（コード→(名称,単価)）
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long, key As String
    For i = 2 To UBound(vM, 1)
        key = UCase$(Trim$(CStr(vM(i, cKeyM))))
        dict(key) = Array(CStr(vM(i, cNameM)), Val(vM(i, cPriceM)))
    Next

    '出力配列（明細＋名称＋単価）
    Dim out() As Variant: ReDim out(1 To UBound(vD, 1), 1 To UBound(vD, 2) + 2)
    Dim c As Long: For c = 1 To UBound(vD, 2): out(1, c) = vD(1, c): Next
    out(1, UBound(vD, 2) + 1) = "名称": out(1, UBound(vD, 2) + 2) = "単価"

    Dim r As Long
    For r = 2 To UBound(vD, 1)
        For c = 1 To UBound(vD, 2): out(r, c) = vD(r, c): Next
        key = UCase$(Trim$(CStr(vD(r, 1))))
        If dict.Exists(key) Then
            out(r, UBound(vD, 2) + 1) = dict(key)(0)
            out(r, UBound(vD, 2) + 2) = dict(key)(1)
        Else
            out(r, UBound(vD, 2) + 1) = "#N/A"
            out(r, UBound(vD, 2) + 2) = 0
        End If
    Next

    '書き戻し
    rgD.Resize(UBound(out, 1), UBound(out, 2)).Value = out

    '外部ファイルを閉じる
    wbM.Close SaveChanges:=False
End Sub
VB

初心者向け解説

  • 外部ファイルを開く: Workbooks.Open("パス", ReadOnly:=True) で安全に開く。
  • 見出し名で列特定: FindHeader 関数を使うと列順が変わっても壊れない。
  • 辞書に格納: キー（コード）を正規化して「名称・単価」を保存。
  • JOIN処理: 明細のコードをキーにして辞書から名称・単価を付与。
  • 未一致補完: 辞書にないコードは「#N/A」「0」で埋める。
  • 閉じる: wbM.Close SaveChanges:=False で外部ファイルを閉じる。

例題で練習

'例1：外部マスタをVLOOKUPでJOIN
Sub Example_External_VLookup()
    JoinMaster_VLookup_External
End Sub

'例2：外部マスタを辞書化して高速JOIN
Sub Example_External_Dict()
    JoinMaster_Dictionary_External
End Sub

'例3：見出し名で安全にJOIN（列順変更に強い）
Sub Example_External_ByHeaders()
    JoinMaster_ByHeaders_External
End Sub
VB
スポンサーリンク
タイトルとURLをコピーしました