Remove a VBA Project Reference

Question

In VBA I can see three different references for PDFCreator. One of them (see the second image) is a version of the software stored locally, and it works. I'd like to use this reference.

The other two are references to versions stored on a server, and they're broken (at this stage, I don't have permission to reinstall or delete them).

My problem is that after selecting the desired reference (see the second image) and clicking 'Ok', it resets to an incorrect reference, as shown in the third image.

How can I either override whatever's going on and select the desired reference or remove the incorrect references? While I'm not able to uninstall these versions from the server, I see no reason that my Excel would need to reference them. Can they just be removed from the list?

Image 1: Default state of the VBA Project References (PDFCreator not selected)

Image 2: Selecting the correct PDFCreator version

Image 3: Re-opening the menu shows that the incorrect PDFCreator version is selected

Interesting, I think it would be better to try [late binding](http://peltiertech.com/Excel/EarlyLateBinding.html) for this bug as a workaround — Sgdva, Jan 27 '17 at 03:54
I dont understand why you cant just remove the broken reference? — brettdj, Jan 27 '17 at 05:32
@brettdj is it possible to remove a broken reference non-programmatically? Given that it's a one-off, removing it with code seems overkill — Sam Woolerton, Jan 27 '17 at 08:36
@sgdva do you think it would still try pull in from the broken references though rather than the good one? — Sam Woolerton, Jan 27 '17 at 08:37
@brettdj I doubt that I can use `ref.isbroken` to programmatically check it as it's a valid filepath, just the install isn't working correctly. I'll give it a shot when I get back in the office though — Sam Woolerton, Jan 27 '17 at 08:44
I'd recommend you use late binding as @Sgdva suggests, remove all references and add the correct one programmatically via AddFromFile: `vbProj.References.AddFromFile "PathToYourDLL"` — LocEngineer, Jan 27 '17 at 11:58

score 1 · Answer 1 · answered Feb 02 '17 at 14:42

You might be able to something like the following...

To Remove broken references:

Private Sub RemoveBrokenReferences(ByVal Book As Workbook)

'////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Dim oRefS As Object, oRef As Object
'////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Set oRefS = Book.VBProject.References
For Each oRef In oRefS
    If oRef.IsBroken Then
        Call oRefS.Remove(oRef)
    End If
Next

End Sub

To Remove a specific reference:

Use something like:

Call ActiveWorkbook.VBProject.References.Remove(oReference)

and you can get the oReference from:

Private Function GetReferenceFromPath(ByVal FilePathName As String) As Object

'////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Dim oFs As Object, oReferenceS As Object, oReference As Object
Dim sFileName  As String, sRefFileName As String
'////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Set oFs = Interaction.CreateObject("Scripting.FileSystemObject")

sFileName = oFs.GetFileName(FilePathName)
Set oReferenceS = ActiveWorkbook.VBProject.References
For Each oReference In oReferenceS
    sRefFileName = oFs.GetFileName(oReference.FullPath)
    If StrComp(sFileName, sRefFileName, vbTextCompare) = 0 Then
        Set GetReferenceFromPath = oReference
    End If
Next

End Function

Does `References.Remove` untick the reference or fully remove it? — Sam Woolerton, Feb 03 '17 at 19:14

Noam Brand · Answer 2 · 2021-02-05T16:18:46.440

Public Sub RemoveReference()
On Error GoTo EH
    Dim RefName As String
    Dim ref As Reference
    RefName = "Selenium"
     
    Set ref = ThisWorkbook.VBProject.References(RefName)
    ThisWorkbook.VBProject.References.Remove ref
    
Exit Sub
EH:
'If an error was encountered, inform the user
    Select Case Err.Number
        Case Is = 9
            MsgBox "The reference is already removed"
        Exit Sub
        Case Is = 1004
            MsgBox "You probably do not have to have Trust Access To Visual Basic Project checked or macros enabled"
        Exit Sub
       Case Else
         'An unknown error was encountered
            MsgBox "Error in 'RemoveReference'" & vbCrLf & vbCrLf & Err.Description
    End Select
End Sub

P.S It is not possible to remove A MISSING/ broken references programmatically after MISSING occurs, only before it happens or manually after it happens. Most cases of MISSING/ broken references are caused because the type library has never before been registered on that system. See How to Remove Reference programmatically?

score -1 · Answer 3 · answered Jan 10 '21 at 14:38

I had a broken reference problem with a large number of Excel spreadsheets when I uninstalled Flash (which for some unknown reason I had included as a reference). I got round the problem as follows:

BE CAREFUL BECAUSE THIS INVOLVES A REGISTRY HACK AN IS COMPLICATED. MAKE A BACKUP OF REGISTRY BEFORE HACKING.

I wrote VBA code to find the Guid of the broken reference. I used Regedit to insert a DUMMY TypeLib entry as follows:

D27CDB6B-AE6D-11CF-96B8-444553540000 was the Guid of the Broken Reference.

HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000} HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000}\1.0 Adobe Acrobat 7.0 Browser Control Type Library 1.0 HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000}\1.0\0 HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000}\1.0\0\win32 C:\Program Files (x86)\Common Files\Adobe\Acrobat\ActiveX\AcroPDF.dll HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000}\1.0\FLAGS 0 HKEY_CLASSES_ROOT\TypeLib{D27CDB6B-AE6D-11CF-96B8-444553540000}\1.0\HELPDIR C:\Program Files (x86)\Common Files\Adobe\Acrobat\ActiveX\

I based the above on another TypeLib entry.

Then I wrote VBA code to read each Reference.Guid in turn and if the Guid matched {D27CDB6B-AE6D-11CF-96B8-444553540000} to remove the reference using References.Remove Reference.

Code for doing this is available all over the forums so I won't repeat here.

After modifying all the affected Workbooks I reinstated the saved registry.

Hope this works for you.

BE CAREFUL BECAUSE THIS INVOLVES A REGISTRY HACK AN IS COMPLICATED. MAKE A BACKUP OF REGISTRY BEFORE HACKING.

Remove a VBA Project Reference

3 Answers3

Linked