How install library in VBA Excel?

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() 
     
     
     
    Dim strGUID As String, theRef As Variant, i As Long 
     
     
    strGUID = "{00020905-0000-0000-C000-000000000046}" 
     
     
    On Error Resume Next 
     
     
    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 
     
     
    Err.Clear 
     
     
    ThisWorkbook.VBProject.References.AddFromGuid _ 
    GUID:=strGUID, Major:=1, Minor:=0 
     
     
    Select Case Err.Number 
    Case Is = 32813 
         
    Case Is = vbNullString 
         
    Case Else 
         
        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 1823 times.