VBA Express : Excel - Determine the File Path or GUID to a VBA Reference Library

Excel

Determine the File Path or GUID to a VBA Reference Library

Ease of Use

Easy

Version tested with

97, 2003

Submitted by:

Ken Puls

Description:

This code returns the full path and Globally Unique Identifier (GUID) to each library referenced in the current project (as selected in VBE|Tools|References). These items can be used to install a VBA Reference Library on the fly.

Discussion:

This code is useful for determining specific information which can be used to add VBA references using code. The advantage of using the GUID is that it does not change with the version of the program. ie. Microsoft Excel's object model GUID is {00020813-0000-0000-C000-000000000046}, whether you are using Excel 97, 2000, 2002, etc... The advantage of using the full file path is that the library doesn't actually have to be installed/registered with Office... ie you can just copy the reference file to another computer and use it without running a full install. NOTE: In Office 2002 or later, the TRUST ACCESS TO VISUAL BASIC PROJECT box MUST be checked, or the code will not work. (This box is located in Tools|Options|Security|Macro Security|Trusted Publishers)

Code:

instructions for use

			Option Explicit 
 
Sub ListReferencePaths() 
     '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
     
    On Error Resume Next 
    Dim i As Long 
    With ThisWorkbook.Sheets(1) 
        .Cells.Clear 
        .Range("A1") = "Reference name" 
        .Range("B1") = "Full path to reference" 
        .Range("C1") = "Reference GUID" 
    End With 
    For i = 1 To ThisWorkbook.VBProject.References.Count 
        With ThisWorkbook.VBProject.References(i) 
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name 
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath 
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID 
        End With 
    Next i 
    On Error GoTo 0 
End Sub

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

In Excel press Alt + F11 to enter the VBE.
From the Tools Menu (in the VBE), choose References.
Find the reference you want to set, check the box and click OK.
Press F5, choose 'ListReferencePaths' and click Run.
Press Alt + Q to close the VBE.
Review the list on the worksheet to see the full file path and GUIDs for all the reference libraries you currently have selected.

Sample File:

RefID.zip 7.86KB

Approved by mdmackillop

This entry has been viewed 1314 times.