About This Article
This article is generated using an automated workflow powered by generative AI. It re-verifies Microsoft Learn’s VBA reference settings and Office security documentation along with existing Daily-Code-Samples, organizing a procedure to safely list broken references.
Verification Status: 📘 Official Information Confirmed – Excel Physical Device Not Verified
Overview
When VBA suddenly stops compiling on another PC, it is worth suspecting MISSING references in the reference settings. By enumerating ThisWorkbook.VBProject.References and checking IsBroken, you can isolate broken references from your code safely.
Check References Macro
Run the following macro to check the reference settings in the Immediate Window.
Option Explicit
Public Sub CheckReferences()
Dim ref As Object
Dim brokenCount As Long
On Error GoTo AccessDenied
Debug.Print "[START] 参照設定を確認します"
For Each ref In ThisWorkbook.VBProject.References
If ref.IsBroken Then
brokenCount = brokenCount + 1
Debug.Print "[BROKEN]", "壊れた参照"
Else
Debug.Print "[OK]", ref.Name, ref.Major & "." & ref.Minor
End If
Next ref
Debug.Print "[RESULT] Broken=" & brokenCount
Exit Sub
AccessDenied:
Debug.Print "[FAILED]", Err.Number, Err.Description
End Sub
This operation uses access to the VBA project object model. On corporate PCs, prioritize organizational policies and do not change settings arbitrarily.
Execution Flow
flowchart TD
A[Referencesを列挙] --> B{IsBroken?}
B -- No --> C[Name / Versionを表示]
B -- Yes --> D[BROKENとして記録]
C --> E[次の参照]
D --> E
Handling Broken References Safely
With broken references, property retrieval such as FullPath may fail when IsBroken = True. Therefore, avoid reading FullPath directly for broken references to prevent runtime errors.
If Not ref.IsBroken Then
Debug.Print ref.Name, ref.FullPath
End If
Why Reference Breaks Occur
Typically, these include library differences when moving to another PC, Office/ActiveX component differences, and missing reference file destinations. When a reference breaks, compile errors may occur near standard functions that appear unrelated, making listing useful as an initial triage step.
When Using for Work
- It is safer not to proceed all the way to automatically “repairing” reference settings.
- Only read and list first.
- Do not automatically delete even if MISSING is detected.
- Do not arbitrarily select one from multiple candidates.
- Record GUIDs and versions to check the cause.
- Do not loosen Office trust settings without permission.
Official Sources and GitHub Sample
- GitHub – Daily-Code-Samples: VBA reference audit
- Microsoft Learn – Reference.FullPath
- Microsoft Support – Change macro security settings in Excel
この記事の更新履歴
この記事は、生成AIを活用した自動レビュー・更新フローにより内容を見直し、必要な修正を反映しています。
2026年9月26日
- 変更参照設定の確認手順とコードブロック周辺の文章の崩れを修正し、可読性を向上させました。
- 変更見出しタグとして誤って配置されていた段落テキストを適切な見出し構造に再構成しました。

