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 1825 times.

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