Having Trouble deleting a missing reference

[navtejsidhu]

Hi,

I am relatively new to Excel so any help you could provide would be
greatly appreciated. I have a macro that updates various (approx 550)
that have been sent out to users. There was a problem with the
templates and I need to delete missing references. The code I use to do
this is shown below.

Const ver As Integer = 1

Dim worksheetversion As Integer
Dim currentRef As Object
Dim response As Integer
Dim i As Integer
Dim theRef As Variant

worksheetversion = wb.Worksheets("ENTITY_INFO").Cells(1, 2).value

If Not (worksheetversion < ver) Then Exit Sub

For i = wb.VBProject.References.Count To 1 Step -1
Set theRef = wb.VBProject.References.Item(i)
If theRef.IsBroken = True Then
'On Error Resume Next
wb.VBProject.References.Remove theRef
'On Error GoTo 0
End If
Next i

In the code, wb refers to a different workbook that is open and
unprotected.

In the for loop, when I get to the reference that is missing I get the
following error: 'Object Library not registered'. It loops through all
the references fine, but is erroring out only on the missing
references. Any ideas what might be going wrong?

 

[navtejsidhu]

I thought I would also mention that the specific library I am trying to
remove is Microsoft ActiveX Objects 2.8 Library.

 

[JLGWhiz]

The Remove method is normally used to remove an item from a collection of
objects. Removing and adding references is normally accomplished by opening
the VBE (Visual Basic Editor) and selecting the Tools menu > References and
then either check to add or uncheck to delete the references as appropriate.
You can open the VBE with Alt + F11. I offer this advice since I don't
understand your question.

 

[navtejsidhu]

JLGWhiz,

Thanks for the response . Basically a previous employee distributed a
workbook with some macros to various users. There were some changes
that need to be done on those workbooks but while doing the changes I
was running into some trouble and realized there was a missing
reference to the Microsoft ActiveX Data Objects 2.8 library. So, I used
the above code to go through all references in the distributed
workbooks to delete any MISSING references (by missing I mean
references that are marked as missing when I go to Tools->References).

In the loop when I reach anything that is marked as missing, I get the
error that I previously mentioned.

I hope this clarifies the problem.

Regards