如何以编程方式删除引用? [英] How to Remove Reference programmatically?

问题描述

我已经使用Excel 2016作为Citrix应用程序编写了VBA代码，该应用程序按预期运行.

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

但是，当我在普通桌面上的Excel 2010中运行此宏时，遇到了参考问题.一旦删除引用，它就会运行.

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.

我要删除显示为缺少:ALTEntityPicker 1.0类型库"的引用；在运行时使用VBA.

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

我尝试了以下操作:

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 

推荐答案

无法以编程方式在发生MISSING之后删除MISSING/损坏的引用，只能在发生之前或之后手动删除.造成大多数MISSING/引用损坏的原因是因为类型库从未在该系统上注册.

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.

预防:通过 Workbook_BeforeClose 事件删除任何有问题的引用，并将其重新添加到 Workbook_Open 事件中，从而避免事前丢失参考/损坏的引用.在示例中，'Selenium'是导致错误的引用，因此我使用 Workbook_BeforeClose 将其删除，然后将其重新添加到 Workbook_Open 上.如果无法添加，则不会添加，也不会出现"MISSING"；如果可以添加，则将添加.

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.

防止由于用户安装了旧版本的MS Office(MS Outlook)而导致Excel VBA编译错误?

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

这篇关于如何以编程方式删除引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！