VBA Libraries

Libraries are used to access functionality outside of what VBA can do on its own. There are a number of VBA libraries provided by the Windows operating system, Office applications, and other software installed by the user. Libraries are often used to control a specific application or access classes which serve specific purposes. Libraries can be used to write VBA programs that utilize multiple applications, access the user's file system, manipulate text files and databases, get information from the internet, and more.

Set Library References

Setting a reference to a library allows a VBA project to access the members of the library directly. Setting a reference allows the VBA compiler and the Visual Basic Editor to see the members of a library. Setting a reference allows for early-binding of object variables, enables intellisense, and makes the library visible in the Object Browser. To set a reference to a library navigate to Tools → References in the Visual Basic Editor and check the library and click Ok. Members of a library can be accessed without a reference by using late-binding (declaring an object variable with the generic Object type and using the CreateObject function with the class's ProgID). Early-binding vs late-binding is discussed in the Objects section.

Fully Qualified Identifiers

When using libraries, be sure to use the fully qualified names for classes, variables, and procedures to avoid naming collisions. There are times when a library contains an identifier that is the same as another identifier in VBA or another library. If the name is not fully qualified, VBA will not know which identifier is being referred to and errors can occur. To fully qualify an identifier's name include the library of origin.

Public Sub Example()

    'References set to Excel object library and Word object library

    Dim ExcelApp As Excel.Application
    Dim WordApp  As Word.Application

    Set ExcelApp = Excel.Application
    Set WordApp = Word.Application

End Sub

Exploring Libraries

To explore the contents of a library in detail set a reference to the library and use the Object Browser to view the library's members.

Search and Browse Specific Libraries

Specific libraries can be browsed and searched by selecting the library from the library drop-down.

View Class Members

Select an item in the Classes section and view its members in the Members section.

Search

Use the search bar to search for specific text related to a class or member. Select an item in the Search Results to view its members in the members section.

Important Libraries

There are a number of important libraries which are very commonly used and are often necessary for accomplishing tasks in VBA.

Microsoft Office Applications

To work with more than one Office application in a single VBA program it is helpful to set references to all Office applications being used.

Adobe Acrobat

The Adobe Acrobat Library can be used to automate the Adobe Acrobat application and work with PDF files. Adobe Acrobat must be purchased from Adobe and installed in order for the VBA libraries to be available.

Micro Focus Reflection

The Micro Focus Reflection libraries can be used to automate tasks in the Micro Focus Reflection application. Micro Focus Reflection Desktop 16.1 must be purchased and installed in order for the libraries to be available.

Programmatically Work With Project References

To programmatically work with project references use the Microsoft Visual Basic for Applications Extensibility 5.3 Library (VBIDE).

'In Immediate Window:

?SetProjectReferenceByFilePath("C:\Windows\System32\scrrun.dll")

PrintProjectReferences ThisWorkbook.VBProject

Option Explicit

Public Sub SetProjectReferenceByFilePath(FilePath As String)

    'Sets Project Reference

    Dim VBProj    As Object 'VBIDE.VBProject
    Dim Refs      As Object 'VBIDE.References
    Dim Ref       As Object 'VBIDE.Reference
    Dim RefFound  As Boolean

    Set VBProj = ThisWorkbook.VBProject
    Set Refs = VBProj.References

    For Each Ref In Refs
        If Ref.FullPath = FilePath Then
            RefFound = True
            Exit For
        End If
    Next Ref

    If Not RefFound Then
        VBProj.References.AddFromFile FilePath
    End If

End Sub

Public Sub PrintProjectReferences(Proj As Object)
                                 'Proj As VBIDE.VBProject

    Dim Ref As Object 'VBIDE.Reference

    For Each Ref In Proj.References
        With Ref
            Debug.Print "'''''''''''''''''''''''''''''''''''''''''''''''''"
            Debug.Print "Name: " & .Name
            Debug.Print "Major Version: " & .Major
            Debug.Print "Minor Version: " & .Minor
            Debug.Print "Description: " & .Description
            Debug.Print "FullPath: " & .FullPath
            Debug.Print "GUID: " & .GUID
            Debug.Print "Builtin: " & .BuiltIn
            Debug.Print "Type: " & .Type
            Debug.Print "'''''''''''''''''''''''''''''''''''''''''''''''''"
        End With
    Next Ref

End Sub