Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – ノーコード差分ツール

Excel VBA
スポンサーリンク

ねらい：設定シートを書くだけで「差分チェック」が回る世界にする

「旧データと新データの差分を取りたい」
「マスタの更新内容を確認したい」
「システムAの出力とシステムBの出力が本当に同じか確かめたい」

こういう“差分チェック”を、そのたびに関数やマクロで組んでいませんか。
ノーコード差分ツールのゴールは、ここを一気に楽にすることです。

どのシートとどのシートを比べるか。
どの列をキーにするか。
どの列の差分を見たいか。
結果をどのシートに出したいか。

これを全部「設定シート」に書くだけで、VBAのコードは一切触らずに差分チェックが走る。
そのための“汎用差分エンジン”をテンプレとして作ります。

全体設計：差分設定シート＋汎用差分エンジン

シート構成のイメージ

基本の構成は、次の三種類です。

旧データシート（例：Old）
新データシート（例：New）
差分結果シート（例：Diff）

そして、それらをどう比べるかを書く「差分設定シート（ConfigDiff）」を用意します。
VBA側は、「ConfigDiff を読み、そこに書かれたルール通りに Old と New を比べて Diff に出す」だけの汎用エンジンです。

ポイントは、「差分のロジック（どの列をキーに、どの列を比較するか）」をコードに書かないこと。
すべて ConfigDiff に書き出してしまうことです。

差分設定シートの設計：何をどう書くか

1行＝1つの差分ルールという形にする

ConfigDiff シートに、次のような列を用意します。

A列：有効フラグ（Y の行だけ処理）
B列：LeftSheet（左側シート名。旧データなど）
C列：RightSheet（右側シート名。新データなど）
D列：KeyCols（キーにする列。カンマ区切り）
E列：CompareCols（比較する列。カンマ区切り）
F列：OutputSheet（差分結果シート名）
G列：Mode（差分モード。ALL／CHANGED／ONLY_LEFT／ONLY_RIGHT など）

例として、こんな感じをイメージしてください。

A: Y
B: Old
C: New
D: A,B
E: C,D,E
F: Diff
G: ALL

これは、「Old と New を A列＋B列で突き合わせ、C〜E列の値の違いを Diff に出す。差分の種類は全部（変更・片側のみ）出す」という意味になります。

ここまで決めてしまえば、「どんな差分を取りたいか」はすべて表で表現できます。
VBAは、この表を読み取って動くだけです。

コア部品：行を「キー→行データ」にまとめる汎用関数

キーを文字列として組み立てる

複数列をキーにするために、「行の中からキー文字列を作る」関数を用意します。

' ModDiffEngine.bas
Option Explicit

Private Function BuildKeyFromRow(ByVal rowData As Variant, ByVal keyCols As Variant) As String
    Dim i As Long
    Dim parts() As String
    ReDim parts(1 To UBound(keyCols) + 1)
    
    For i = LBound(keyCols) To UBound(keyCols)
        parts(i + 1) = CStr(rowData(1, CLng(keyCols(i))))
    Next
    
    BuildKeyFromRow = Join(parts, "||")
End Function
VB

keyCols は「{1,2}」のような列番号配列を想定しています。
キーは「キー1||キー2」のような文字列になります（区切り文字は被らないものにしておく）。

左右のシートを Dictionary に載せる

差分を取るために、「キー→行データ」を Dictionary に載せます。

Private Function LoadSheetToDict( _
        ByVal sheetName As String, _
        ByVal keyCols As Variant) As Object
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sheetName)
    
    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Then
        Set LoadSheetToDict = CreateObject("Scripting.Dictionary")
        Exit Function
    End If
    
    Dim data As Variant
    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    
    For r = 2 To lastRow
        key = BuildKeyFromRow(Application.Index(data, r, 0), keyCols)
        If key <> "" Then
            If Not dict.Exists(key) Then
                dict.Add key, Application.Index(data, r, 0)
            End If
        End If
    Next
    
    Set LoadSheetToDict = dict
End Function
VB

ここでの重要ポイントは、「行全体（1行分）を丸ごと配列として Dictionary に持たせている」ことです。
これで、キーからその行の全列にアクセスできるようになります。

差分ルールを読み込む：ConfigDiff → ルール配列

差分ルール1行分の型を定義する

Private Type DiffRule
    Enabled As Boolean
    LeftSheet As String
    RightSheet As String
    KeyCols As Variant
    CompareCols As Variant
    OutputSheet As String
    Mode As String
End Type
VB

列番号は数字で扱いたいので、列記号（A,B,C…）でも番号に変換できるようにします。

Private Function ColToNumber(ByVal colRef As Variant) As Long
    If IsNumeric(colRef) Then
        ColToNumber = CLng(colRef)
    Else
        ColToNumber = Range(CStr(colRef) & "1").Column
    End If
End Function
VB

カンマ区切りの列指定「A,B,C」を列番号配列に変換する関数も用意します。

Private Function ParseCols(ByVal s As String) As Variant
    Dim parts As Variant
    parts = Split(s, ",")
    
    Dim cols() As Long
    ReDim cols(0 To UBound(parts))
    
    Dim i As Long
    For i = 0 To UBound(parts)
        cols(i) = ColToNumber(Trim$(parts(i)))
    Next
    
    ParseCols = cols
End Function
VB

ConfigDiff から DiffRule 配列を作る

Private Function LoadDiffRules() As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("ConfigDiff")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then
        LoadDiffRules = Empty
        Exit Function
    End If
    
    Dim data As Variant
    data = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 7)).Value
    
    Dim rules() As DiffRule
    ReDim rules(1 To UBound(data, 1))
    
    Dim i As Long
    For i = 1 To UBound(data, 1)
        rules(i).Enabled = (UCase$(CStr(data(i, 1))) = "Y")
        rules(i).LeftSheet = CStr(data(i, 2))
        rules(i).RightSheet = CStr(data(i, 3))
        rules(i).KeyCols = ParseCols(CStr(data(i, 4)))
        rules(i).CompareCols = ParseCols(CStr(data(i, 5)))
        rules(i).OutputSheet = CStr(data(i, 6))
        rules(i).Mode = UCase$(CStr(data(i, 7)))
    Next
    
    LoadDiffRules = rules
End Function
VB

これで、「ConfigDiff の内容」が DiffRule の配列としてメモリに載ります。

汎用差分エンジン：設定に従って差分を取る

速度アップ用のラッパー

Private Sub SpeedOn()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Private Sub SpeedOff()
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

メイン処理：ルールごとに差分をかける

Public Sub RunDiffTool()
    Dim rules As Variant
    rules = LoadDiffRules()
    If IsEmpty(rules) Then
        MsgBox "ConfigDiffに差分設定がありません。", vbInformation
        Exit Sub
    End If
    
    SpeedOn
    
    Dim i As Long
    For i = LBound(rules) To UBound(rules)
        If rules(i).Enabled Then
            ApplyDiffRule rules(i)
        End If
    Next i
    
    SpeedOff
    MsgBox "ノーコード差分ツールの処理が完了しました。", vbInformation
End Sub
VB

1つの差分ルールを適用する汎用処理

ここが心臓部です。

Private Sub ApplyDiffRule(ByRef rule As DiffRule)
    Dim dictL As Object, dictR As Object
    dictL = Nothing: dictR = Nothing
    
    Set dictL = LoadSheetToDict(rule.LeftSheet, rule.KeyCols)
    Set dictR = LoadSheetToDict(rule.RightSheet, rule.KeyCols)
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = ThisWorkbook.Worksheets(rule.OutputSheet)
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = rule.OutputSheet
    Else
        wsOut.Cells.Clear
    End If
    
    Dim maxCols As Long
    maxCols = Application.WorksheetFunction.Max( _
        ThisWorkbook.Worksheets(rule.LeftSheet).Cells(1, Columns.Count).End(xlToLeft).Column, _
        ThisWorkbook.Worksheets(rule.RightSheet).Cells(1, Columns.Count).End(xlToLeft).Column)
    
    wsOut.Cells(1, 1).Value = "DiffType"
    wsOut.Cells(1, 2).Value = "Key"
    
    Dim outRow As Long
    outRow = 2
    
    Dim k As Variant
    
    For Each k In dictL.Keys
        Dim inRight As Boolean
        inRight = dictR.Exists(k)
        
        If Not inRight Then
            If rule.Mode = "ALL" Or rule.Mode = "ONLY_LEFT" Then
                wsOut.Cells(outRow, 1).Value = "ONLY_LEFT"
                wsOut.Cells(outRow, 2).Value = CStr(k)
                outRow = outRow + 1
            End If
        Else
            If rule.Mode = "ALL" Or rule.Mode = "CHANGED" Then
                If HasChanged(dictL(k), dictR(k), rule.CompareCols) Then
                    wsOut.Cells(outRow, 1).Value = "CHANGED"
                    wsOut.Cells(outRow, 2).Value = CStr(k)
                    outRow = outRow + 1
                End If
            End If
        End If
    Next
    
    For Each k In dictR.Keys
        If Not dictL.Exists(k) Then
            If rule.Mode = "ALL" Or rule.Mode = "ONLY_RIGHT" Then
                wsOut.Cells(outRow, 1).Value = "ONLY_RIGHT"
                wsOut.Cells(outRow, 2).Value = CStr(k)
                outRow = outRow + 1
            End If
        End If
    Next
    
    wsOut.Columns.AutoFit
End Sub
VB

ここでは、まず左側のキーを全部見て、「右にない」か「右にあるが中身が違う」かを判定しています。
次に、右側のキーを見て、「左にない」ものを拾っています。

差分の種類は、DiffType 列に

ONLY_LEFT（旧にだけある）
ONLY_RIGHT（新にだけある）
CHANGED（両方にあるが中身が違う）

として出しています。

値が変わっているかどうかを判定する関数

CompareCols に指定された列だけを見て、「違うかどうか」を判定します。

Private Function HasChanged( _
        ByVal rowL As Variant, _
        ByVal rowR As Variant, _
        ByVal compareCols As Variant) As Boolean
    
    Dim i As Long
    For i = LBound(compareCols) To UBound(compareCols)
        Dim c As Long
        c = compareCols(i)
        If CStr(rowL(1, c)) <> CStr(rowR(1, c)) Then
            HasChanged = True
            Exit Function
        End If
    Next
    HasChanged = False
End Function
VB

ここでの重要ポイントは、「どの列を比較するかも設定で決められる」ことです。
ConfigDiff の CompareCols を変えるだけで、「金額だけ見る」「名称だけ見る」「全部見る」を切り替えられます。

例題：顧客マスタの旧版と新版の差分を取る

想定するシート構成

Old シート（旧顧客マスタ）

A列：顧客コード
B列：顧客名
C列：住所
D列：電話番号

New シート（新顧客マスタ）

同じ列構成で、新しいデータが入っているとします。

やりたいことは、

顧客コードをキーにして、
旧にしかない顧客、新にしかない顧客、
顧客名・住所・電話番号のどれかが変わった顧客を洗い出すことです。

ConfigDiff シートには、次のように書きます。

A列：Y
B列：Old
C列：New
D列：A
E列：B,C,D
F列：Diff
G列：ALL

これで、「Old と New を A列（顧客コード）で突き合わせ、B〜D列の違いを Diff に出す」という設定になります。

RunDiffTool を実行すると、Diff シートには

DiffType
Key

という2列が出て、行ごとに

ONLY_LEFT（旧にだけいる顧客）
ONLY_RIGHT（新にだけいる顧客）
CHANGED（両方にいるが、名前・住所・電話のどれかが変わった顧客）

が一覧で出ます。

ここからさらに、「どの項目がどう変わったか」まで出したければ、
HasChanged の中身を拡張して、「変更前」「変更後」を別列に出すようにすればOKです。
それも、CompareCols を使って柔軟に制御できます。

重要ポイントの深掘り：ノーコード差分ツールを“現場で回る”形にするコツ

ConfigDiff の「検証」を入れておくべき理由

完全ノーコードにすると、現場の人が自由に設定を変えられる反面、
シート名のタイプミス、列指定のミス、Mode の誤記なども起こりやすくなります。

実務で使うなら、RunDiffTool の最初に

指定された LeftSheet／RightSheet／OutputSheet が存在するか
KeyCols／CompareCols に指定された列が存在するか
Mode が許可された値（ALL／CHANGED／ONLY_LEFT／ONLY_RIGHT）か

をチェックし、問題があれば処理を止めてメッセージを出す、という“安全装置”を付けておくと安心です。

キーの正規化（全角半角・大文字小文字）をどこでやるか

差分の精度は、「キーがどれだけ揃っているか」に依存します。
顧客コードや商品コードのような“きれいなコード”ならそのままで良いですが、
メールアドレスや名称などをキーにする差分では、正規化が必要です。

その場合は、BuildKeyFromRow の中で

値を Trim する
全角半角を揃える
大文字小文字を揃える

などの処理を挟むか、別途 NormalizeKey 関数を作って通すようにします。
重要なのは、「左と右で同じ正規化をかける」ことです。

差分の粒度をどうするか（行単位か項目単位か）

今回のテンプレは、「行単位で差分があるかどうか」を判定しています。
もし「どの項目が変わったかまで知りたい」なら、HasChanged を拡張して

変わった列名
旧値
新値

を Diff シートに出すようにすれば、項目単位の差分一覧が作れます。

ここも、「CompareCols に何を指定するか」で柔軟に制御できます。
まずは行単位の CHANGED だけで運用を始め、必要になったら項目単位の出力を追加する、という段階的な導入がおすすめです。

まとめ：ノーコード差分ツールは「差分ロジックを表にして、コードはエンジンに閉じ込める」発想

このテンプレの本質は、次の三つです。

どのシート同士を、どの列をキーに、どの列を比較するかを、ConfigDiff という表にすべて書き出す。
VBA側は、その表を読み取り、配列と Dictionary を使って高速に差分を取る汎用エンジンだけを持つ。
差分内容を変えたいときは、ConfigDiff の行を追加・修正するだけで、コードは一切触らない。

これができると、「顧客マスタの差分」「商品マスタの差分」「CSVの旧版と新版の差分」など、
差分パターンがいくつ増えても、ツールは1本で済みます。

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