How to Remove Reference programmatically?

Question

I've written VBA code using Excel 2016 as a Citrix application, which runs as intended.

When I run this macro in Excel 2010 on a normal desktop, however, I'm facing a reference issue. Once I remove the reference it runs.

I want to remove the reference which is showing as "Missing: ALTEntityPicker 1.0 Type Library" during runtime using VBA.

I tried the following:

Sub DeleteRef(RefName)
    Dim ref As Reference

    'You need a reference to remove
    Set ref = References("Missing: ALTEntityPicker 1.0 Type Library")
    References.Remove ref
End Sub 

Answer 1

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.

Prevention: Avoid MISSING/ broken references beforehand by removing any problematic reference by Workbook_BeforeClose event and adding it back on Workbook_Open event. In the example 'Selenium' is a reference that causes an error so I remove it with Workbook_BeforeClose and add it back on Workbook_Open. If it can't be added it would not be added and no MISSING will appear, If it can be added it will be added.

Preventing Excel VBA compile errors due to users having an older version of MS Office (MS Outlook) installed?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Cancel = True Then Exit Sub
    RemoveReference
End Sub

Private Sub Workbook_Open()
 AddReferences
End Sub

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

---

Public Sub AddReferences()
    Dim wbk As Workbook
    Set wbk = ActiveWorkbook

    AddRef wbk, "{0277FC34-FD1B-4616-BB19-A9AABCAF2A70}", "Selenium"
End Sub

Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String)
    Dim i As Byte
    On Error GoTo EH
    With wbk.VBProject.References
        For i = 1 To .Count
            If .item(i).Name = sRefName Then
               Exit For
            End If
        Next i
        If i > .Count Then
           .AddFromGuid sGuid, 0, 0 ' 0,0 should pick the latest version installed on the computer
        End If
    End With

Exit Sub

EH:
'If an error was encountered, inform the user
    Select Case Err.Number
        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 'AddRef'" & vbCrLf & vbCrLf & Err.Description
    End Select
End Sub

---

Public Sub ExistingRefs()
 Dim i As Byte
 On Error GoTo EH
      With Application.ThisWorkbook.VBProject.References
        For i = 1 To .Count
            Debug.Print "    AddRef wbk, """ & .item(i).GUID & """, """ & .item(i).Name & """"
        Next i
    End With
    
Exit Sub
EH:
'If an error was encountered, inform the user
    Select Case Err.Number
        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 'ExistingRefs'" & vbCrLf & Err.Description
    End Select
End Sub

Answer 2

Try this

Sub DeleteRef(RefName) 
    Dim ref As Reference

    'You need a reference to remove '
    Set ref = References("Missing: ALTEntityPicker 1.0 Type Library")
    vbProj.References.Remove ref
End Sub