🔧

CheckDependencies

ユーティリティ

VBAプロジェクトの名前参照設定の依存関係を確認する

Excelブック内のセル参照関係（依存関係）をVBAで調査するマクロです。Precedents（参照元）とDependents（参照先）プロパティを使って特定のセルがどのセルを参照しているか、またどのセルから参照されているかを追跡します。数式の複雑な依存チェーンを可視化し、変更の影響範囲を事前に把握するための診断ツールとして活用できます。内部ではRange.PrecedentsおよびRange.Dependentsプロパティを再帰的にたどることで、間接的な参照関係まで含めて追跡しています。FindCellWithFormulaと組み合わせれば、ブック内の数式セルをすべて洗い出した上で、それぞれの依存関係を可視化するという2段階の診断フローが構築できます。数式が複雑に絡み合った古いブックほど、この可視化の効果は大きくなります。

📖 使い方

VBAエディタを開く（Alt + F11）
モジュールを挿入（挿入 > モジュール）
下記VBAコードをコピー&ペースト
ブック上で実行（Alt + F8 でマクロ選択）

💡 実行例：実行すると参照設定の参照先リストとステータスがシートに出力される

🎯 活用シーン

✓大規模モデルのセル変更前に影響を受けるセルを事前確認
✓引継ぎ資料として複雑な数式の参照関係マップを自動生成
✓エラー伝播の根本原因を追跡するためのデバッグ支援ツール
✓長年運用された複雑な予算モデルで、あるセルを変更した場合の影響範囲を事前に把握したいとき
✓前任者が作った数式だらけのシートを引き継ぐ際に、計算ロジックの全体像を理解したいとき

💡 実装のヒント

▸Range.Precedents で直接・間接の参照元セルを取得できます
▸Range.Dependents で参照先のセルを確認できます
▸ShowPrecedentsメソッドで追跡矢印をシート上に表示することもできます
▸ShowPrecedentsメソッドで表示される追跡矢印は、Worksheet.ClearArrowsメソッドで消去できます。
▸大規模なブックでは依存関係の探索に時間がかかるため、対象範囲を絞って段階的に調査することをお勧めします。

💻 VBAコード

' CheckDependencies
' -----------------
' Macro Name: CheckDependencies
' Description: VBAプロジェクトの名前参照設定の依存関係を確認する
' Parameters: なし
' Returns: なし
' Usage: 実行すると参照設定の参照先リストとステータスがシートに出力される
' -----------------

Sub CheckDependencies()
    Dim vbProj As Object
    Dim ref As Object
    Dim ws As Worksheet
    Dim outputRow As Long
    Dim missingCount As Long
    Dim brokenList As String

    On Error GoTo ErrorHandler

    On Error Resume Next
    Set vbProj = ActiveWorkbook.VBProject.References
    On Error GoTo ErrorHandler
    If vbProj Is Nothing Then
        MsgBox "この操作には VBA プロジェクトへのアクセス許可が必要です。", vbCritical
        Exit Sub
    End If

    Set ws = ActiveSheet
    outputRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If outputRow < 1 Then outputRow = 1
    outputRow = outputRow + 2

    ws.Cells(outputRow, 1).Value = "VBA 参照設定一覧"
    ws.Cells(outputRow, 1).Font.Bold = True
    ws.Cells(outputRow, 1).Font.Size = 14
    outputRow = outputRow + 1

    ws.Cells(outputRow, 1).Value = "名前"
    ws.Cells(outputRow, 2).Value = "バージョン"
    ws.Cells(outputRow, 3).Value = "状態"
    ws.Cells(outputRow, 1).Font.Bold = True
    ws.Cells(outputRow, 2).Font.Bold = True
    ws.Cells(outputRow, 3).Font.Bold = True
    ws.Range(ws.Cells(outputRow, 1), ws.Cells(outputRow, 3)).Interior.Color = RGB(200, 200, 200)
    outputRow = outputRow + 1

    missingCount = 0
    brokenList = ""

    For Each ref In ActiveWorkbook.VBProject.References
        Dim refName As String
        Dim refVer As String
        refName = ref.Name
        refVer = ref.Major & "." & ref.Minor & "." & ref.Build

        If ref.IsBroken Then
            ws.Cells(outputRow, 1).Value = refName
            ws.Cells(outputRow, 2).Value = refVer
            ws.Cells(outputRow, 3).Value = "参照不可"
            ws.Cells(outputRow, 3).Font.Color = RGB(255, 0, 0)
            ws.Cells(outputRow, 1).Interior.Color = RGB(255, 230, 230)
            ws.Cells(outputRow, 2).Interior.Color = RGB(255, 230, 230)
            ws.Cells(outputRow, 3).Interior.Color = RGB(255, 230, 230)
            missingCount = missingCount + 1
            brokenList = brokenList & "  - " & refName & vbCrLf
        Else
            ws.Cells(outputRow, 1).Value = refName
            ws.Cells(outputRow, 2).Value = refVer
            ws.Cells(outputRow, 3).Value = "OK"
            ws.Cells(outputRow, 3).Font.Color = RGB(0, 128, 0)
        End If
        outputRow = outputRow + 1
    Next ref

    ws.Columns("A:C").AutoFit

    If missingCount > 0 Then
        MsgBox "参照設定チェック完了。" & vbCrLf & missingCount & " 件の参照不可が見つかりました。" & vbCrLf & vbCrLf & brokenList, vbExclamation
    Else
        MsgBox "参照設定チェック完了。問題なし。", vbInformation
    End If
    Exit Sub

ErrorHandler:
    If Err.Number = 9 Then
        MsgBox "この操作には VBA プロジェクトへのアクセス許可が必要です。" & vbCrLf & "ツール > 参照設定を確認してください。", vbCritical
    Else
        MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    End If
End Sub

🔗 同カテゴリの関連マクロ