Check References


Home           Programming       Favorites      

The following procedure will check for broken references.  It will display a message box either saying that broken references were found or that they were not found.

Accidental deletion would be a likely cause of broken references.
Sub CheckReferences()
  On Error Resume Next

  Dim VBAEditor As VBIDE.VBE
  Dim VBProj As VBIDE.VBProject
  Dim strPrompt As String

  Set VBAEditor = Application.VBE
  Set VBProj = VBAEditor.ActiveVBProject

  Dim chkRef

  ' Check through the selected references
  'in the References dialog box.
  For Each chkRef In VBProj.References
    If chkRef.IsBroken Then
      Err.Clear
      VBProj.References.Remove chkRef
        If Err.Number <> 0 Then
          strPrompt = "There appears to be a "
          strPrompt = _ 
          strPrompt & "missing reference."
          strPrompt = _ 
          strPrompt & vbCrLf & vbCrLf
          strPrompt = _ 
          strPrompt & "Click OK, then use alt-F11"
          strPrompt = _ 
          strPrompt & "to open the Visual Basic Editor."
          strPrompt = _ 
          strPrompt & vbCrLf & vbCrLf
          strPrompt = _ 
          strPrompt & "Then go to Tools->References."
          strPrompt = _ 
          strPrompt & vbCrLf & vbCrLf
          strPrompt = _ 
          strPrompt & "If you see the word ""MISSING"" "
          strPrompt = _ 
          strPrompt & " on any reference, uncheck it."
          strPrompt = _ 
          strPrompt & vbCrLf & vbCrLf
          strPrompt = _ 
          strPrompt & "Then exit and try "
          strPrompt = _ 
          strPrompt & "to add it back again."

           MsgBox strPrompt

           Exit Sub
        End If

      Else
      End If
  Next
  MsgBox "No Broken References were detected"

End Sub