How to remove missing references via script in Excel VBA?

Question

I have excel that is use by many users. Some of them do not have installed all software's and therefore missing references. I am, trying to have script that remove all references that can not be found on C: drive.

I have this code in /Microsoft Excel Objects / ThisWorkbook but is not fully working. Anyone could help me with this.

Sub TestRef()
Dim REF As VBIDE.Reference
Dim WB As Workbook
Set WB = ThisWorkbook
For Each REF In WB.VBProject.References
    If StrComp(Left(REF.FullPath, 1), "C", vbTextCompare) <> 0 Then
        DelRef
        Debug.Print REF.Name, REF.Description, REF.FullPath
    End If
Next REF
End Sub

Sub DelRef()
Dim REF As VBIDE.Reference
Dim WB As Workbook
Set WB = ThisWorkbook
With ThisWorkbook.VBProject.References
    .Remove .Item("MSForms")
End With    
End Sub

Missing Referece Image

Just out of curiosity: when you are removing the missing references. Doesn't this also mean that the code in the file won't work and shouldn't run? Are you (hence) also removing the underlying code? How about writing an automated email to the system administrator when a broken reference is found (instead of removing it)? Then the system admin can install the missing software. At the bottom of the following solution you'll find a short snippet to automatically generate an Outlook email (in which you could request installation of the missing software): stackoverflow.com/a/30346959/1153513 — Ralph
– Ralph, Commented Apr 28, 2017 at 9:07
this is good idea as well I will try to add this as well. Some people run codes that link to external software but all people must be able to open and see current status and run normal excel operation. There are dedicated Click to link with external tools. — Michal
– Michal, Commented Apr 28, 2017 at 9:12

Shai Rado · Accepted Answer · 2017-04-28 09:11:18Z

1

Not with your variables names, but something I wrote a while back and I've been using for a while to remove "Missing" references :

Dim theRef As Variant, i As Long

' loop through all References in VB Project
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)

    ' if reference is "Missing" >> remove it to avoid error message
    If theRef.isbroken = True Then
        ThisWorkbook.VBProject.References.Remove theRef
    End If

    ' just for Debug
    ' Debug.Print theRef.Description & ";" & theRef.FullPath & ";" & theRef.isbroken & vbCr
Next i

edited Apr 28, 2017 at 9:11

answered Apr 28, 2017 at 8:54

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Michal Over a year ago

thanks, when I run I have itallic Run-time error '-2147319779' Method 'FullPath' of object 'Reference' failed

Shai Rado Over a year ago

@Michal ok, and without the Debug line ? I've made it as a comment

Shai Rado Over a year ago

@Tom I will, I need to "clean" it up a little (it inside something related to work), and one I have I'll upload it to my GitHub rep, ok ? link to my profile github.com/shairad

Shai Rado Over a year ago

@Tom try the link to me rep. github.com/shairad/Excel-VBA-VBAProject-Model-

Michal Over a year ago

@ShaiRado still Run-time error '-2147319779' Object library not registered this error in on this line ThisWorkbook.VBProject.References.Remove theRef

|

Noam Brand · Accepted Answer · 2021-09-10 16:29:49Z

0

Removing a missing reference will not solve the problem because the reference is needed for some propose or it wouldn't have been added in the first place.

Another way of dealing with missing reference: Closing the Excel file if it has a missing reference, then complete the Object library installation of the needed reference by Excel VBA in a different Excel file.

You may notify the user there is a missing part of the installation and suggest closing the file to complete the installation.

See: Catch 22 -Installing reference library (Selenium) by Excel VBA

edited Sep 10, 2021 at 16:29

answered Sep 10, 2021 at 16:18

Noam Brand

3463 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to remove missing references via script in Excel VBA?

2 Answers 2

17 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related