Excel

Add a VBA Reference Library via code

Ease of Use

Intermediate

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

This code allows you to add reference to a specific library on the fly. Useful if you have users who share workbooks between different versions of Excel. 

Discussion:

Assume that your users share workbooks between Office 97 and Office 2000 (or higher). When the 97 user sends the file on, the workbook opens fine, and the reference libraries are automatically updated, but when they are returned to the 97 user, the reference libraries are listed as "missing". (This happens because they've been upgraded to a newer version that Office 97 does not recognize.) This code will remove any missing references, and then add a reference on the fly, based on the GUID you specify. (The GUID provided below will add a reference to the Microsoft Word library, and is NOT VERSION SPECIFIC!) NOTE: In Office 2002 or later, the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, MUST be checked, or the code will not work. For a KB Entry to get the GUID for the library you need, see http://www.vbaexpress.com/kb/submitcode.php?kb_id=278. 

Code:

instructions for use

			

Sub AddReference() 'Macro purpose: To add a reference to the project using the GUID for the 'reference library Dim strGUID As String, theRef As Variant, i As Long 'Update the GUID you need below. strGUID = "{00020905-0000-0000-C000-000000000046}" 'Set to continue in case of error On Error Resume Next 'Remove any missing references For i = ThisWorkbook.VBProject.References.Count To 1 Step -1 Set theRef = ThisWorkbook.VBProject.References.Item(i) If theRef.isbroken = True Then ThisWorkbook.VBProject.References.Remove theRef End If Next i 'Clear any errors so that error trapping for GUID additions can be evaluated Err.Clear 'Add the reference ThisWorkbook.VBProject.References.AddFromGuid _ GUID:=strGUID, Major:=1, Minor:=0 'If an error was encountered, inform the user Select Case Err.Number Case Is = 32813 'Reference already in use. No action necessary Case Is = vbNullString 'Reference added without issue Case Else 'An unknown error was encountered, so alert the user MsgBox "A problem was encountered trying to" & vbNewLine _ & "add or remove a reference in this file" & vbNewLine & "Please check the " _ & "references in your VBA project!", vbCritical + vbOKOnly, "Error!" End Select 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. Expand the desired project (if required) until you see the ThisWorkbook object.
  5. Double click the ThisWorkbook object.
  6. Paste code into the right pane.
  7. Update the strGUID to the GUID of the library you need. (See KB entry at http://www.vbaexpress.com/kb/submitcode.php?kb_id=278 if you need to discover the GUID of your library)
  8. Press Alt + Q to close the VBE.
  9. Save workbook before any other changes.
 

Test the code:

  1. In Excel press Alt + F11 to enter the VBE.
  2. Go to the Tools menu and select References.
  3. Verify that the reference you want is NOT checked.
  4. Run the AddReference routine
  5. Go back to the Tools menu and select References.
  6. Verify that the reference you want IS checked.
 

Sample File:

AddRef.zip 10.83KB 

Approved by mdmackillop


This entry has been viewed 1659 times.

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