Excel

Remove Missing VBA Library References via code

Ease of Use

Intermediate

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

This code can be used to remove a reference Library that is marked at "Missing" in the VBE References window. It's best use is for removing "Missing" references which have occured because the reference libraries have been "upgraded" by sending the file to a newer version of the program (say 2002), and then back to the original version again (say 97). 

Discussion:

This code is best run as part of the Workbook_Open event, before any attempts are made to add new references via code. NOTE: It will generate an error if the code encounters a reference library that has never been registered (installed on that system) before. 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 

			
*** In a Standard Module *** 
Option Explicit 
 
Sub References_RemoveMissing() 
     'Macro purpose:  To remove missing references from the VBE
     
    Dim theRef As Variant, i As Long 
     
    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 
     
    If Err <> 0 Then 
        MsgBox "A missing reference has been encountered!" _ 
        & "You will need to remove the reference manually.", _ 
        vbCritical, "Unable To Remove Missing Reference" 
    End If 
     
    On Error GoTo 0 
End Sub 
 
*** In the ThisWorkbook Module *** 
Private Sub Workbook_Open() 
    Call References_RemoveMissing 
End Sub

How to use:

  1. Copy the References_RemoveMissing subroutine.
  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 the code into the right pane.
  7. Copy the Workbook_Open event from the window above.
  8. Locate the project's "ThisWorkbook" module in the Project Explorer and double click it.
  9. Paste the code into the right pane.
  10. Press Alt + Q to close the VBE.
  11. Save workbook before any other changes.
 

Test the code:

  1. Download the attached file and open up the VBE (Alt + F11)
  2. In the highest version of Office that you have access to, set a reference to the desired library. (Tools References)
  3. Save the file.
  4. Send it to another PC with a lower version of Office.
  5. The file will list all references, and should strip all invalid references upon opening.
  7. ALTERNATELY (if you have the lower version of office)
  8. Download the attached file and open up the VBE (Alt + F11)
  9. Set a reference to the desired library. (Tools References)
  10. Save the file.
  11. Send it to a computer with a higher version of office, and get the user to open it. (All references will automatically be upgraded.)
  12. Have the user save the file and return it to you.
  13. The file will list all references, and should strip all invalid references upon opening.
  14. (If you need to reset them, see http://www.vbaexpress.com/kb/getapprovalarticle.php?kb_id=267)
 

Sample File:

RemoveRef.zip 10.49KB 

Approved by mdmackillop


This entry has been viewed 536 times.

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