Solved

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

Posted on 2008-10-12
3
1,479 Views
Last Modified: 2013-11-25
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
Comment
Question by:Clemens_Saur
3 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 22698111
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22698262
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
 

Author Comment

by:Clemens_Saur
ID: 22805934
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 53
Add and format columns in vb6 7 26
TT Column Arrange 10 28
VLOOKUP Function MS Excel 2010 2 22
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now