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:

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

Test the code:

  1. In Excel press Alt + F11 to enter the VBE.
  2. From the Tools Menu (in the VBE), choose References.
  3. Find the reference you want to set, check the box and click OK.
  4. Press F5, choose 'ListReferencePaths' and click Run.
  5. Press Alt + Q to close the VBE.
  6. 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 1271 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express