VBA references - list, add & remove


In this module - how to programmatically:

  1. List available VBA references
  2. Add VBA references to the project
  3. Delete VBA references from the project

0. VBA References


The VBA reference list (figure 1) can be accessed by the VBE menu Tools > References sequence. The Available References list displays each reference item by its description property, in priority order.


VBA reference list
Fig 1: References - VBAProject - with nine references selected, identified by description property, and location path popup displayed

References are also part of the libraries drop-down in the Object Browser (figure 2). Each selected reference list item is shown by its name property, in alphabetical order, in the Object Browser.


VBA object browser libraries
Fig 2: References - Object Browser library - with nine references listed in alphabetical order of Name property

1. VBA References - List


Code 1 uses the Reference object and its associated References collection, to print a list of available references. This code requires the Microsoft Visual Basic for Applications Extensibility 5.3 reference.



Code 1: macro xlfVBEListReferences print reference list to immediate window 
Sub xlfVBEListReferences()
' Requires References :: Microsoft Visual Basic for Applications Extensibility 5.3
'                        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Dim oRef As VBIDE.Reference   ' Item
Dim oRefs As VBIDE.References ' Collection
Dim i As Integer

Set oRefs = Application.VBE.ActiveVBProject.References

    Debug.Print "Print Time: " & Time & " :: Item - Name and Description"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.Name, oRef.Description
        Next oRef
    Debug.Print vbNewLine

    i = 0
    Debug.Print "Print Time: " & Time & " :: Item - Full Path"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.FullPath
        Next oRef
    Debug.Print vbNewLine

    i = 0
    ' List the Globally Unique Identifier (GUID) for each library referenced in the current project
    Debug.Print "Print Time: " & Time & " :: Item - GUID"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.GUID
        Next oRef
    Debug.Print vbNewLine

End Sub

About Code 1

  1. Line 14: Debug.Print - for each reference, print the Item number, Name and Description properties to the Immediate Window
  2. Line 22: Debug.Print - for each reference, print the Item number, and FullPath property to the Immediate Window
  3. Line 31: Debug.Print - for each reference, print the Item number, GUID (Globally Unique Identifier) property to the Immediate Window

  4. See figure 3 for the Immediate Window details

references sample 9
Fig 3: Immediate Window - with Debug.Print text for Name and Description, Full Path, and GUID properties

Reference objects, Items 1 and 9, are expanded in the Locals Window view of Figure 4 as the oRefs object from code 1.


add references guid locals
Fig 4: Locals Window - oRef items - expanded view for Item 1 and Item 9

2. VBA References - Add


The Reference items can be created in the References Collection with the AddFromFile (code 2a) or AddFromGuid (code 2b) methods.



Code 2a: macro xlfVBEAddReferences add a Reference object to the References collection 
Sub xlfVBEAddReferences()
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References

    On Error GoTo OnError
    oRefs.AddFromFile "C:\Windows\System32\msxml6.dll"

OnError:
End Sub

Code 2b: macro xlfVBEAddReferencesGUID add a Reference object to the References collection 

Sub xlfVBEAddReferencesGUID()
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References

    On Error GoTo OnError
        ' Syntax: AddFromGuid(Guid, Major, Minor)
        ' The Major version number of the reference.
        ' The Minor version number of the reference.
        ' Microsoft XML, v6.0 :: Major - 6, Minor - 0
    oRefs.AddFromGuid "{F5078F18-C551-11D3-89B9-0000F81FE221}", 6, 0

OnError:
End Sub

3. VBA References - Remove


To remove an item from the collection, loop through with For Each ... Next, and identify specific objects by Name (code 3a) or Description (code 3b) then Remove.



Code 3a: macro xlfVBERemoveReference1 - reference name version 
Sub xlfVBERemoveReference1()
Dim oRef As Reference
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References

    For Each oRef In oRefs
        If oRef.Name = "MSXML2" Then
            oRefs.Remove oRef
            Exit For
        End If
    Next oRef

End Sub

Code 3b: macro xlfVBERemoveReference2 - reference description version 

Sub xlfVBERemoveReference2()
Dim oRef As Reference
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References

    For Each oRef In oRefs
        If oRef.Description = "Microsoft XML, v6.0" Then
            oRefs.Remove oRef
            Exit For
        End If
    Next oRef

End Sub