时间戳

首页 > 解决方案 > 如何以编程方式删除引用？

excel - 如何以编程方式删除引用？

问题描述

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

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

我想在运行时使用 VBA 删除显示为“Missing: ALTEntityPicker 1.0 Type Library”的引用。

我尝试了以下方法：

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

标签： excelvba

解决方案

在 MISSING 发生之后，不能以编程方式删除 A MISSING/损坏的引用，只能在它发生之前或在它发生之后手动删除。大多数丢失/损坏引用的情况是由于类型库以前从未在该系统上注册过而引起的。

预防：通过按事件删除任何有问题的引用Workbook_BeforeClose并将其重新添加到事件中，预先避免丢失/损坏的引用Workbook_Open。在示例'Selenium'中是一个导致错误的引用，因此我将其删除Workbook_BeforeClose并重新添加到Workbook_Open. 如果不能添加则不会添加，不会出现MISSING，如果可以添加则添加。

防止由于用户安装了旧版本的 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

推荐阅读