Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1502
  • Last Modified:

How to disable VBA Error Messages due to broken or missing preferences

When an Excel sheet with Macros is passed from one PC to another, it may not work due to broken or missing preferences.
Ideally I would like to check for broken links, remove them, if possible reconnect them, and possibly dump offending code.
Unfortunately I cannot do any of that, because as soon as I trigger any macro it crashed on the unrelated offending sub-routine.

Any suggestions?

Example: Removing Broken References.... but I first have to disable the error messages...
 
Dim theRef As Variant, i As Long
On Error Resume Next
For i = 1 To Application.References.Count 'Remove any missing references
Set theRef = Application.References.Item(i)
If theRef.IsBroken = True Then
Application.References.Remove (theRef)
End If
Next i

Open in new window

0
Clemens_Saur
Asked:
Clemens_Saur
1 Solution
 
Dave BrettCommented:
You could try this approach from Dennis Wallentin. Pls note that you need to add a reference to Microsoft Visual Basic for Applications Extensibility 5.3. I have attached the notes below posted by Dennis when he provide this code in another forum

Regards

Dave

#1 A reference ISMISSING is added to the workbook VB-project references.
(Should per se not be a problem unless You call it by code or try to install it then
an error-message will be showed.

#2 The below procedure must be executed from a different workbook and the target workbook must be open.

If running Excel 2002 and above You must allow access to VB-projects via Tools - Macros... - Security.

If the add-ins VB-project is closed then You are out of luck...


VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Sub Del_Broken_Ref()
     'Add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3
    Dim VBReferens As VBIDE.Reference
    Dim VBProjekt As VBIDE.VBProject
    Dim stBok As String
     
    stBok = Workbooks("Test.xls").Name
     
    Set VBProjekt = Workbooks(stBok).VBProject
     
    For Each VBReferens In VBProjekt.References
        If VBReferens.IsBroken Then VBProjekt.References.Remove VBReferens
    Next VBReferens
     
End Sub

Open in new window

0
 
Patrick MatthewsCommented:
Clemens_Saur said:
>>Any suggestions?

Yes: wherever possible, use late binding instead of early binding.  Late binding tends to be highly tolerant of
changes in versions, whereas with early binding, sometimes you are OK, and sometimes not.
0
 
Clemens_SaurAuthor Commented:
The solution was good for removing broken reference links. The original code was not effective. The new code is perfect.
The trick to avoiding 'early crashes' due to missing reference links however is not to pass any variables which are reference dependent  between subs or functions.
I had passed a variable dimed as CodeModule. CodeModule requires the 5.3 extensions. I changed the variable from  Codemodule to String and later on converted it inside the sub or function back to CodeModule and early crashes were eliminated : )
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now