Check for missing references on opening

[Cresta]

Hello

I would like have an Excel file check for missing references as the file is
opening and remove or warn of the missing reference, not stop the show.
At the moment if any references are missing all of the remaining references
don't appear to be installed either, causing code reference errors as the
file continues to open.

Any ideas
Thanks

 

[paul.robinson]

Hi
Have you tried searching this group using,

Check for missing references on opening

regards
Paul

 

[Sandusky]

Hi
Have you tried searching this group using,

Check for missing references on opening

regards
Paul

This assumes his Newsreader allows searching.

 

[Peter T]

There may be a reason for a missing reference, removing it might not solve
your problems. Sometimes if it's an anticipated versioning issue you can
remove the missing ref then re-add it. Following is a direct answer to your
question but be aware it might cause more problems than it solves.

Private Sub Workbook_Open()
Dim bAskFirst As Boolean
Dim bDel As Boolean
Dim oRefs As Object ' References
Dim oRef As Object ' Reference
Dim sDes As String

bAskFirst = True ' ask before deleting

Set oRefs = Me.VBProject.References
On Error Resume Next
For Each oRef In oRefs

sDes = oRef.Description
If Err Then
If bAskFirst Then
bDel = MsgBox( _
"Remove missing ref ?" & VBA.Constants.vbCr & _
oRef.Name, 4) = 6 ' vbYesNo = 4, vbYes = 6
Else
bDel = True
End If

If bDel Then
oRefs.Remove oRef
Debug.Print oRef.Name, "Removed"
End If
Err.Clear
End If
Next

End Sub

In XL2002 and later you'll need to check Trust access to VB projects.

If you anticipate a missing reference fully qualify all VBA libraries like
VBA.Constants in the above. Although unqualified 'Constants' doesn't
generally cause problems unqualified String and DateTime functions probably
will.

Regards,
Peter T

PS
Sandusky - Search this and most ng's in Google groups