VBA Does Not Compile Suddenly? Listing Reference Settings to Find BROKEN References

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

When VBA suddenly stops compiling on another PC, it is worth suspecting the MISSING in the reference settings.ThisWorkbook.VBProject.References By enumerating the IsBroken and checking the

you can isolate broken references from your code.

Get Started First

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.

• Office macro and VBA security settings can be verified on Microsoft Support.

Microsoft Support – Change macro security settings in Excel

Check Here[OK]Display the [BROKEN] and Broken=0 separately in the Immediate Window. If

flowchart TD
    A[Referencesを列挙] --> B{IsBroken?}
    B -- No --> C[Name / Versionを表示]
    B -- Yes --> D[BROKENとして記録]
    C --> E[次の参照]
    D --> E

then no broken references are detected. If there is one or more, cross-reference them with the references settings screen.

Do Not Recklessly Read FullPath on Broken ReferencesIsBroken=TrueWith broken references, property retrieval itself may fail. In the existing complete sample, FullPath do not read the

for references, and attempt GUID retrieval safely in a separate function.

• Reference properties can be verified in the Office VBA official reference.

Microsoft Learn – Reference.FullPath

Try Changing One Placeref.FullPathAdditionally display the

If Not ref.IsBroken Then
    Debug.Print ref.Name, ref.FullPath
End If

only for normal references.IsBrokenFirst, by adding one display item while keeping the

determination, you can observe broken references without increasing risky property accesses.

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

Separating auditing and repair can reduce accidents involving replacing with incorrect references.

GitHub Sample

• The complete version includes normal reference paths, safe GUID retrieval for broken references, and display upon access denial.

Daily-Code-Samples – VBA reference audit

• Official and Primary Information

• Microsoft Learn – Reference.FullPath

Microsoft Support – Change macro security settings in Excel

ConclusionReferencesVBA reference troubleshooting becomes easier to isolate by first reading and visualizing IsBroken and