|
|
|
|
|
|
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()
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.
|
|