Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 監査ログツール

Excel VBA
スポンサーリンク

ねらい：Excelマクロの「誰が・いつ・何をしたか」を残す“監査ログ”の型を作る

マクロが便利になればなるほど、あとからこういうことを聞かれる場面が増えます。

誰がこのファイルをいつ更新したのか。
このボタンを押したのは誰か。
このデータが書き換わったのはいつか。

ここで目指す「監査ログツール」は、難しいことはせずに、次のような“型”を作ることです。

マクロの中で「重要な操作」が行われたタイミングで、専用のログシートに
日時、ユーザー名、処理名、対象シート・セル、前後の値、結果（OK/NG）、メッセージ
を1行追記していく。

これをテンプレ化しておけば、どんなマクロにも簡単に「監査ログ機能」を組み込めるようになります。

設計：監査ログシートの構造と「1行の意味」を決める

監査ログシートの列設計

まずは、ログを書き込む専用シートを1枚用意します。名前は「AuditLog」とします。
1行目にヘッダを置き、2行目以降にログを追記していく形です。

列の例は次の通りです。

A列：LogID（連番）
B列：LogTime（日時）
C列：UserName（Windowsユーザー名など）
D列：Action（処理名。例：IMPORT_CSV、UPDATE_MASTER など）
E列：TargetSheet（対象シート名）
F列：TargetRange（対象セル範囲。例：A2、A2:C10 など）
G列：BeforeValue（変更前の値。必要に応じて）
H列：AfterValue（変更後の値）
I列：Result（OK / NG）
J列：Message（エラー内容や補足）

ここで大事なのは、「1行が1つの“出来事”を表す」という考え方です。
マクロの中で「ここは後から追跡したい」というポイントごとに、この1行を追加していきます。

共通基盤：監査ログを書き込む汎用プロシージャ

監査ログ1行を書き込む Sub

まずは「ログ1行を書く」ことだけに特化した共通部品を作ります。

' ModAudit_Base.bas
Option Explicit

Public Sub WriteAuditLog(ByVal actionName As String, _
                         ByVal targetSheet As String, _
                         ByVal targetRange As String, _
                         Optional ByVal beforeValue As String = "", _
                         Optional ByVal afterValue As String = "", _
                         Optional ByVal result As String = "OK", _
                         Optional ByVal message As String = "")
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("AuditLog")
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "AuditLog"
        ws.Range("A1:J1").Value = Array("LogID", "LogTime", "UserName", "Action", "TargetSheet", "TargetRange", "BeforeValue", "AfterValue", "Result", "Message")
    End If
    
    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    Dim logId As Long
    If nextRow = 2 Then
        logId = 1
    Else
        logId = ws.Cells(nextRow - 1, "A").Value + 1
    End If
    
    ws.Cells(nextRow, "A").Value = logId
    ws.Cells(nextRow, "B").Value = Now
    ws.Cells(nextRow, "C").Value = Environ$("Username")
    ws.Cells(nextRow, "D").Value = actionName
    ws.Cells(nextRow, "E").Value = targetSheet
    ws.Cells(nextRow, "F").Value = targetRange
    ws.Cells(nextRow, "G").Value = beforeValue
    ws.Cells(nextRow, "H").Value = afterValue
    ws.Cells(nextRow, "I").Value = result
    ws.Cells(nextRow, "J").Value = message
    
    ws.Columns("A:J").AutoFit
End Sub
VB

ここでの重要ポイントを少し深掘りします。

監査ログシートが存在しなければ自動で作るようにしておくと、「シートを作り忘れてエラー」が起きません。
LogID は単純な連番で構いませんが、「前行の値＋1」で振ることで、途中で行を削除しても一意性が保たれます。
UserName は Environ$("Username") を使うと、Windowsログオン名が取れます。社内で「誰が押したか」を知るには十分なことが多いです。
Result と Message を分けておくことで、「成功ログ」と「エラーログ」を後からフィルタしやすくなります。

この Sub を「どのマクロからも呼べる共通部品」として置いておくのが、監査ログツールの土台です。

例題1：マクロ開始・終了をログに残す（処理単位の監査）

処理の入口と出口にログを挟む

まずは一番簡単な使い方として、「マクロの開始と終了」をログに残す例を見てみます。

' ModSample_Process.bas
Option Explicit

Public Sub Import_Csv_Sample()
    Call WriteAuditLog("IMPORT_CSV_START", "N/A", "N/A", "", "", "OK", "CSV取込開始")
    
    On Error GoTo ErrHandler
    
    ' ここにCSV取込処理を書く（例示）
    ' ...
    
    Call WriteAuditLog("IMPORT_CSV_END", "Data", "A1", "", "", "OK", "CSV取込正常終了")
    Exit Sub

ErrHandler:
    Call WriteAuditLog("IMPORT_CSV_ERROR", "Data", "A1", "", "", "NG", "エラー: " & Err.Number & " - " & Err.Description)
    Err.Clear
End Sub
VB

この例では、次の3つのタイミングでログを書いています。

処理開始時（IMPORT_CSV_START）
正常終了時（IMPORT_CSV_END）
エラー発生時（IMPORT_CSV_ERROR）

これだけでも、「いつ誰がこのマクロを走らせたか」「エラーで止まったのか、正常に終わったのか」が一目で分かるようになります。

ここで大事なのは、「ログを書くこと自体は軽い処理なので、遠慮せずに入れていい」という感覚です。
特にエラー時のログは、あとから原因を追うときの命綱になります。

例題2：セルの値変更をログに残す（データ変更の監査）

Before/After を取ってから書き込む

次に、「特定のセルの値をマクロで書き換えるとき、その前後の値をログに残す」例を見てみます。

' ModSample_Update.bas
Option Explicit

Public Sub Update_MasterCell()
    Dim ws As Worksheet
    Set ws = Worksheets("Master")
    
    Dim tgt As Range
    Set tgt = ws.Range("B2")
    
    Dim beforeVal As String
    beforeVal = CStr(tgt.Value)
    
    Dim newVal As String
    newVal = "新しい値"
    
    tgt.Value = newVal
    
    Call WriteAuditLog("UPDATE_MASTER_CELL", ws.Name, tgt.Address(False, False), beforeVal, newVal, "OK", "Master!B2 を更新")
End Sub
VB

ここでの重要ポイントは、「書き換える前に必ず BeforeValue を取る」ことです。
マクロが値を上書きしてしまうと、元の値はもう戻ってきません。
監査ログに Before/After を残しておけば、「いつ、何から何に変わったか」を後から確認できます。

実務では、1セルだけでなく、範囲全体を更新することも多いので、範囲版のテンプレも用意しておくと便利です。

範囲更新用テンプレ：複数セルの変更をまとめてログに残す

範囲の値を配列で保持してから更新する

複数セルを一括で更新する場合、「全部の Before/After をログに残す」のは現実的ではありません。
そこで、「代表的な情報だけをログに残す」型を作ります。

Public Sub Update_Block_WithLog()
    Dim ws As Worksheet
    Set ws = Worksheets("Master")
    
    Dim rng As Range
    Set rng = ws.Range("B2:D10")
    
    Dim beforeSummary As String
    beforeSummary = "Rows=" & rng.Rows.Count & ", Cols=" & rng.Columns.Count
    
    Dim v As Variant
    v = rng.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) * 1.1
            End If
        Next
    Next
    
    rng.Value = v
    
    Dim afterSummary As String
    afterSummary = "Rows=" & rng.Rows.Count & ", Cols=" & rng.Columns.Count & ", Updated=Numeric*1.1"
    
    Call WriteAuditLog("UPDATE_BLOCK", ws.Name, rng.Address(False, False), beforeSummary, afterSummary, "OK", "数値を1.1倍に更新")
End Sub
VB

ここでは、BeforeValue と AfterValue に「範囲の概要」を入れています。
全セルの中身を文字列にして詰め込むこともできますが、ログが巨大になりすぎて現実的ではありません。

監査の目的が「どの範囲にどんな処理をかけたか」を知ることなら、
このようなサマリ情報でも十分役に立ちます。

応用：監査ログを「別ファイル」や「日付ごと」に分割する

ログが増えすぎる問題への対処

長く運用していると、AuditLog シートの行数がどんどん増えていきます。
数万行を超えると、フィルタや検索が重くなってきます。

そこで、少しレベルを上げて「ログを別ブックに切り出す」テンプレも考えられます。

考え方はシンプルです。

ログを書き込む先を「このブックの AuditLog シート」ではなく、「ログ専用ブック」にする。
ログ専用ブックは、日付ごとや月ごとにファイルを分ける。
WriteAuditLog の中で、「今日の日付からログファイル名を決めて開く→追記→閉じる」という流れにする。

イメージコードだけ示すと、次のようになります。

Public Sub WriteAuditLog_ToFile(ByVal actionName As String, _
                                ByVal targetSheet As String, _
                                ByVal targetRange As String, _
                                Optional ByVal beforeValue As String = "", _
                                Optional ByVal afterValue As String = "", _
                                Optional ByVal result As String = "OK", _
                                Optional ByVal message As String = "")
    Dim logFolder As String
    logFolder = ThisWorkbook.Path & "\AuditLogs"
    
    If Dir(logFolder, vbDirectory) = "" Then
        MkDir logFolder
    End If
    
    Dim logFile As String
    logFile = logFolder & "\Audit_" & Format(Date, "yyyymmdd") & ".xlsx"
    
    Dim wbLog As Workbook
    If Dir(logFile) = "" Then
        Set wbLog = Workbooks.Add
        wbLog.Worksheets(1).Name = "AuditLog"
        wbLog.Worksheets(1).Range("A1:J1").Value = Array("LogID", "LogTime", "UserName", "Action", "TargetSheet", "TargetRange", "BeforeValue", "AfterValue", "Result", "Message")
        wbLog.SaveAs Filename:=logFile, FileFormat:=xlOpenXMLWorkbook
    Else
        Set wbLog = Workbooks.Open(logFile, ReadOnly:=False)
    End If
    
    Dim ws As Worksheet
    Set ws = wbLog.Worksheets("AuditLog")
    
    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    Dim logId As Long
    If nextRow = 2 Then
        logId = 1
    Else
        logId = ws.Cells(nextRow - 1, "A").Value + 1
    End If
    
    ws.Cells(nextRow, "A").Value = logId
    ws.Cells(nextRow, "B").Value = Now
    ws.Cells(nextRow, "C").Value = Environ$("Username")
    ws.Cells(nextRow, "D").Value = actionName
    ws.Cells(nextRow, "E").Value = targetSheet
    ws.Cells(nextRow, "F").Value = targetRange
    ws.Cells(nextRow, "G").Value = beforeValue
    ws.Cells(nextRow, "H").Value = afterValue
    ws.Cells(nextRow, "I").Value = result
    ws.Cells(nextRow, "J").Value = message
    
    ws.Columns("A:J").AutoFit
    
    wbLog.Save
    wbLog.Close False
End Sub
VB

ここまで来ると、監査ログは「日別ファイル」に蓄積されていきます。
本体ブックは軽いまま、ログはログで長期保管できる、という構成です。

落とし穴と対策：監査ログを「邪魔にしない」ための工夫

ログがうるさすぎると誰も見なくなる

何でもかんでもログに書きすぎると、ログがノイズだらけになり、誰も見なくなります。
監査ログの目的は、「あとから必要になったときに、最低限の手がかりが残っていること」です。

そのために意識したいのは次のような線引きです。

マクロ開始・終了、エラー発生など「処理単位」のログは必ず残す。
データ変更は、「重要なマスタ」「金額」「ステータス変更」などに絞る。
ループの1行ごとにログを書くのではなく、「何件処理したか」をまとめて1行に書く。

この“粒度の設計”が、監査ログツールを「生きたログ」にするか、「誰も見ないログ」にするかを分けます。

ユーザーに「監査されている感」を与えすぎない

監査ログは大事ですが、「全部見張られている」と感じさせると、現場の心理的ハードルが上がります。
運用としては、

何のためにログを取っているか（事故調査・トレーサビリティ・品質向上など）を共有する。
個人攻撃ではなく、プロセス改善の材料として使うことを明言する。

といったコミュニケーションもセットで考えると、ツールが受け入れられやすくなります。

まとめ：監査ログも「1行の型」を決めて、どのマクロにも差し込める部品にする

監査ログツールの本質は、「ログ1行の型」を決めて、それを書き込む共通 Sub を用意することです。

AuditLog シート（またはログファイル）に
LogTime、UserName、Action、TargetSheet、TargetRange、Before/After、Result、Message
を1行ずつ積み上げていく。

マクロの開始・終了、エラー発生、重要なデータ変更のタイミングで、
WriteAuditLog を1行差し込むだけで、どのマクロにも監査機能を持たせられます。

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