none
Add Visual Basic Extensibility library through code RRS feed

  • Question

  • can you please tell me where and what the name of the Visual Basic Extensibility 5.3 library is (the dll).

    I would like to add a reference to it from another workbook using vba

    Also I am not sure what the reference name is

    I have this code

    Sub AddReference()

        Dim VBAEditor As VBIDE.VBE
        Dim vbProj As VBIDE.VBProject
        Dim chkRef As VBIDE.Reference
        Dim BoolExists As Boolean
        Set VBAEditor = Application.VBE
        Set vbProj = ActiveWorkbook.VBProject
        '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
        For Each chkRef In vbProj.References
            Debug.Print chkRef.Name
            If chkRef.Name = "Extensibility_53" Then
                BoolExists = True
                GoTo CleanUp
            End If
        Next
        vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll"
    CleanUp:
        If BoolExists = True Then
            MsgBox "Reference already exists"
        Else
            MsgBox "Reference Added Successfully"
        End If
        Set vbProj = Nothing
        Set VBAEditor = Nothing
    End Sub

    thanks

    Tuesday, June 5, 2018 12:11 AM

Answers

  • In VBA you can get a list of the References with the following.

    Sub ListRefPathsGUID()
         'Macro purpose:  To determine full path and Globally Unique Identifier (GUID)
         'to each referenced library.  Select the reference in the Tools\References
         'window, then run this code to get the information on the reference's library
        
        Dim i As Long
       
        For i = 1 To ThisWorkbook.VBProject.References.Count
            With ThisWorkbook.VBProject.References(i)
                Debug.Print .Name & "    " & .FullPath & "    " & .GUID
            End With
        Next i
    End Sub

    You can add a reference with the following code.

    Sub AddRefGuid()
        'Add VBIDE (Microsoft Visual Basic for Applications Extensibility 5.3
       
        ThisWorkbook.VBProject.References.AddFromGuid _
            "{0002E157-0000-0000-C000-000000000046}", 2, 0
     
    End Sub


    Regards, OssieMac

    • Marked as answer by James N San Wednesday, June 6, 2018 12:09 AM
    Tuesday, June 5, 2018 3:28 AM
  • Just to add, if all you want to do is ensure the RegExp reference exists you don't need to add the extensibility reference, just something like this

    Function AddFSOref(wb As Workbook) As Boolean
    Dim ref As Object
    
        On Error Resume Next
        With wb.VBProject.References
            Set ref = .Item("VBScript_RegExp_55")
            If ref Is Nothing Then
                Set ref = .AddFromGuid("{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5)
            End If
        End With
        AddFSOref = Not ref Is Nothing
        
    End Function

    Better to use the GUID as the dll path can vary in different OS

    Note user's security settings must allow access to the 'VBA Project object model', typically it's not enabled. If not known the usual way is to develop with early binding (with references included and fully declared variables), then remove the references and convert to late binding, for example -

    'early binding
    Dim rx As RegExp
    Set rx = New RegExp ' or createObject as below

    'late binding
    Dim rx As Object
    Set rx = CreateObject("VBScript.RegExp")


    • Marked as answer by James N San Wednesday, June 6, 2018 12:09 AM
    Tuesday, June 5, 2018 9:00 PM
    Moderator

All replies

  • In VBA you can get a list of the References with the following.

    Sub ListRefPathsGUID()
         'Macro purpose:  To determine full path and Globally Unique Identifier (GUID)
         'to each referenced library.  Select the reference in the Tools\References
         'window, then run this code to get the information on the reference's library
        
        Dim i As Long
       
        For i = 1 To ThisWorkbook.VBProject.References.Count
            With ThisWorkbook.VBProject.References(i)
                Debug.Print .Name & "    " & .FullPath & "    " & .GUID
            End With
        Next i
    End Sub

    You can add a reference with the following code.

    Sub AddRefGuid()
        'Add VBIDE (Microsoft Visual Basic for Applications Extensibility 5.3
       
        ThisWorkbook.VBProject.References.AddFromGuid _
            "{0002E157-0000-0000-C000-000000000046}", 2, 0
     
    End Sub


    Regards, OssieMac

    • Marked as answer by James N San Wednesday, June 6, 2018 12:09 AM
    Tuesday, June 5, 2018 3:28 AM
  • Just to add, if all you want to do is ensure the RegExp reference exists you don't need to add the extensibility reference, just something like this

    Function AddFSOref(wb As Workbook) As Boolean
    Dim ref As Object
    
        On Error Resume Next
        With wb.VBProject.References
            Set ref = .Item("VBScript_RegExp_55")
            If ref Is Nothing Then
                Set ref = .AddFromGuid("{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5)
            End If
        End With
        AddFSOref = Not ref Is Nothing
        
    End Function

    Better to use the GUID as the dll path can vary in different OS

    Note user's security settings must allow access to the 'VBA Project object model', typically it's not enabled. If not known the usual way is to develop with early binding (with references included and fully declared variables), then remove the references and convert to late binding, for example -

    'early binding
    Dim rx As RegExp
    Set rx = New RegExp ' or createObject as below

    'late binding
    Dim rx As Object
    Set rx = CreateObject("VBScript.RegExp")


    • Marked as answer by James N San Wednesday, June 6, 2018 12:09 AM
    Tuesday, June 5, 2018 9:00 PM
    Moderator
  • excellent thanks
    Wednesday, June 6, 2018 12:09 AM
  • thanks
    Wednesday, June 6, 2018 12:10 AM