Joe Was
MrExcel MVP
- Joined
- Feb 19, 2002
- Messages
- 7,539
The code below removes broken references from a workbooks VBAProject-References. From its workbook it opens the workbook you want to check and deletes any broken references.
The actual code to delete the broken reference runs, but when you check that workbooks References all the removed ones are still listed with check boxes?
I must be doing something wrong?
Can someone run this code and see if you can fix it?
You can run it against a blank dummy workbook that you name in the code below, as long as it has some References selected.
Thanks, JSW.
P.S. I may be off on this and it may only remove the reference wile the two workbooks are open, but not do it permanently?
Sub DelBrokeRef()
'Check a WorkBook for Broken References and
'Delete any that are Broken!
'Run code from its own WorkBook, from a Standard Module.
'You must have a "Reference" in this WorkBook made to:
'Microsoft Visual Basic for Applications Extensibility 5.3 library
'to run this code!
'Add the name of the workbook you want to check, below!
Dim myRefs As VBIDE.Reference
Dim myProj As VBIDE.VBProject
Dim myWBook As String
Dim myCount
Dim myFile As String
On Error GoTo myError
myCount = 0
'Add the name of the workbook you want to check, below:
myFile = "ReferenceCheck.xls"
Workbooks.Open Filename:=myFile
myWBook = Workbooks(myFile).Name
MsgBox myWBook & " Opened for Checking" & Chr(13) & _
"and will now be checked for Broken References!"
Set myProj = Workbooks(myWBook).VBProject
For Each myRefs In myProj.References
If myRefs.IsBroken Then myProj.References.Remove myRefs
MsgBox myRefs.Name & Chr(13) & " Has been found to be Broken!" & _
Chr(13) & Chr(13) & myRefs.Name & Chr(13) & "Has been Removed!"
myCount = myCount + 1
Next myRefs
If myCount > 0 Then
MsgBox "Done Checking for Broken References!" & Chr(13) & _
myCount & ": References Found Broken!"
Else
MsgBox "No Broken References found, Done!"
End If
Exit Sub
The actual code to delete the broken reference runs, but when you check that workbooks References all the removed ones are still listed with check boxes?
I must be doing something wrong?
Can someone run this code and see if you can fix it?
You can run it against a blank dummy workbook that you name in the code below, as long as it has some References selected.
Thanks, JSW.
P.S. I may be off on this and it may only remove the reference wile the two workbooks are open, but not do it permanently?
Sub DelBrokeRef()
'Check a WorkBook for Broken References and
'Delete any that are Broken!
'Run code from its own WorkBook, from a Standard Module.
'You must have a "Reference" in this WorkBook made to:
'Microsoft Visual Basic for Applications Extensibility 5.3 library
'to run this code!
'Add the name of the workbook you want to check, below!
Dim myRefs As VBIDE.Reference
Dim myProj As VBIDE.VBProject
Dim myWBook As String
Dim myCount
Dim myFile As String
On Error GoTo myError
myCount = 0
'Add the name of the workbook you want to check, below:
myFile = "ReferenceCheck.xls"
Workbooks.Open Filename:=myFile
myWBook = Workbooks(myFile).Name
MsgBox myWBook & " Opened for Checking" & Chr(13) & _
"and will now be checked for Broken References!"
Set myProj = Workbooks(myWBook).VBProject
For Each myRefs In myProj.References
If myRefs.IsBroken Then myProj.References.Remove myRefs
MsgBox myRefs.Name & Chr(13) & " Has been found to be Broken!" & _
Chr(13) & Chr(13) & myRefs.Name & Chr(13) & "Has been Removed!"
myCount = myCount + 1
Next myRefs
If myCount > 0 Then
MsgBox "Done Checking for Broken References!" & Chr(13) & _
myCount & ": References Found Broken!"
Else
MsgBox "No Broken References found, Done!"
End If
Exit Sub
