Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Note: This requires "Trust Access to VB Library" enabled which it is not by default

Option Explicit

Public Sub Test_RefCheck()
 Dim RefNameStr As String, RefDllLocStr As String
 RefNameStr = "UIAutomationClient"
 RefDllLocStr = Environ("APPDATA") & "\Microsoft\Excel\XLSTART\UIAutomationClient.dll"
 Call CheckorAddReference(RefNameStr, RefDllLocStr)
End Sub

Public Sub CheckorAddReference(RefNameStr As String, RefDllLocStr As String)
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As VBIDE.Reference
    Dim BoolExists As Boolean, BoolAdded As Boolean

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject

    BoolAdded = False
    BoolExists = False

DoesRefExist:
    '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
    For Each chkRef In ThisWorkbook.VBProject.References
        'Debug.Print chkRef.Name
        If chkRef.Name = RefNameStr Then
            BoolExists = True
            GoTo CleanUp
        End If
    Next
    
    If BoolAdded = False Then
     vbProj.References.AddFromFile RefDllLocStr
     BoolAdded = True
     GoTo DoesRefExist
    End If
    
CleanUp:
    Debug.Print "Checking For Reference " & RefNameStr
    If BoolExists = True And BoolAdded = False Then
        Debug.Print "Reference already exists"
    ElseIf BoolExists = True And BoolAdded = True Then
        Debug.Print "Reference Added Successfully"
    Else
        Debug.Print "Reference Not Added"
    End If

    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub

  • No labels