Using vba to remove a reference to a missing activex dll

This code doesn't remove the missing reference in the attached Project.
 
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!" & vbCrLf _
        & "You will need to remove the reference manually." & vbCrLf _
        & "Error " & Err.Number & " -" & Err.Description, _
        vbCritical, "Unable To Remove Missing Reference"
    End If
     
    On Error GoTo 0
End Sub

Open in new window


This is the error message
 The Error MessageWell of course its not registered -it's missing!

Here is the project with the code as an autorun in workbook.open
 Book11.xls

I can remove the reference manulally using VBE|Tools|References Uncheck so I know it's possible.
How do I change this vba to make it remove the missing refereence to the activex dll

PS
None of the functions in the activex dll are used by the excel project at this point but I need to be able to fix this using vba.

PPS
Access to the project is trusted............
LVL 5
sir plusSales ManagementAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The approach suggested was not to wait for a fail but to pre-emptivley remove   whilst it's still ok and then add again before next use and then all you are doing Is adding the dll normally.

But if you have something good enough then it matters not.

Chris
0
 
Martin LissOlder than dirtCommented:
I do VB6 rather than VBA so I've never run into that but does this a help?rticle
0
 
Martin LissOlder than dirtCommented:
That should have said

does this article help?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sir plusSales ManagementAuthor Commented:
Unfortunately not at all....
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
It looks to me as though with the loop you are not assessing each reference ... on a quick hack as below I have added the 'missing' check to the loop.

Chris
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
        elseIf Err <> 0 Then
            MsgBox "A missing reference has been encountered!" & vbCrLf _
            & "You will need to remove the reference manually." & vbCrLf _
            & "Error " & Err.Number & " -" & Err.Description, _
            vbCritical, "Unable To Remove Missing Reference"
        End If
    Next i
     
     
    On Error GoTo 0
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
A quick test revealed I failed to clear the error creating multiple responses, added a clear on the error code!  I looked for some of my own code from the past, as a potential alternative ... but cannot find it!

Chris
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
        ElseIf Err <> 0 Then
            MsgBox "A missing reference has been encountered!" & vbCrLf _
            & "You will need to remove the reference manually." & vbCrLf _
            & "Error " & Err.Number & " -" & Err.Description, _
            vbCritical, "Unable To Remove Missing Reference"
            Err.Clear
        End If
    Next i
     
     
    On Error GoTo 0
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The following adaption returns info that is more specific to the failures, but it's only a tweak as such.

Chris
Sub References_RemoveMissing()
'Macro purpose:  To remove missing references from the VBE
Dim theRef As Variant
Dim i As Long
Dim str As String
     
    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
        ElseIf Err <> 0 Then
            str = str & "reference: " & theRef.Name & ", (" & theRef.fullpath & ")." & vbCrLf
            Err.Clear
        End If
    Next i
     
    If str <> "" Then
        MsgBox "Missing reference(s) has been encountered!" & vbCrLf _
            & "You will need to remove these references manually." & vbCrLf _
            & vbCrLf & str, vbCritical, "Unable To Remove Missing Reference"
    End If
    On Error GoTo 0
End Sub

Open in new window

0
 
sir plusSales ManagementAuthor Commented:
Hi Chris
Thanks for this
Glad you are onto it.

Yes I thought of trapping the error only the line
theRef.IsBroken = True is the one that fails

Open in new window

is the one that fails
and if that fails then
ThisWorkbook.VBProject.References.Remove theRef

Open in new window

does as well  

That is the real issue
if the dll is "missing but not missing" then the usual preoperties & methods relating the reference (including isbroken & remove) fail

A workround to remove the reference before the referenced file goes missing is hardly acceptable.
I need to be able to fix this once the libriary is "missing"

The issue still remains that when a referenced activex dll is replaced with another activex dll that the usual properties of the rferenece are not available.

Ps
I can't do something like rename the library from the project to make it actually missing in which case as the library is locked by excel.........

Perhaps a loader project to rename the library first then fix the refereence while its actually missing.
IDK -messy & not eloquent

I can fix this manually in vbe by unchecking the reference so i should be able via code.

Can anyone please help.
rorya, Chris B, anyone ...........
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Does the actual DLL exist for sure because if it does we can perhaps try adding it again ... dunno if it will help but trying out the range of techniques available to us.  If so what is the GUID for the ref?

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
For example ...

Chris
Sub AddReference()
' Derived from some code defined in www.vbaexpress.com
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim i As Long
'VBA Library                {000204EF-0000-0000-C000-000000000046}
'Excel Library              {00020813-0000-0000-C000-000000000046}
'stdole Library             {00020430-0000-0000-C000-000000000046}
'Office Library             {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
'Access Library             {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
'VBIDE Library              {0002E157-0000-0000-C000-000000000046}
'MSForms Library            {0D452EE1-E08F-101A-852E-02608C4D0BB4}
'Word Library               {00020905-0000-0000-C000-000000000046}
'Scripting Library          {420B2830-E718-11CF-893D-00A0C9054228}
'VBScript_RegExp_55 Library {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
'MAPI Library               {3FA7DEA7-6438-101B-ACC1-00AA00423326}
'AutoCAD 2008               {851A4561-F4EC-4631-9B0C-E7DC407512C9}
'AutoCAD 2011               {D32C213D-6096-40EF-A216-89A3A6FB82F7}

 
    strGUID = Array("{00020905-0000-0000-C000-000000000046}")
     
     'Set to continue in case of error
    On Error Resume Next
     
     'Remove any missing references
    For i = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(i)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next i
     
     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear
     
     'Add the reference
    For Each itm In strGUID
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        Guid:=itm, 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
    Next
    On Error GoTo 0
End Sub

Open in new window

0
 
smartpictureCommented:
I have what seems to be a similar problem, ie it won't programmatically remove a 'missing' reference, maybe pooling our knowledge will help?

I have an Access mdb with a reference to MS Office 12.0 object library. I have issued it to around 100 users, and it fails for 2 of them. In the startup code, I loop through the references and remove any MISSING ones, then add the correct ones. This works for all other users and all other missing references. For these 2 people and this one reference though, within the loop it won't do ref.name, ref.isbroken etc because it says '<Error loading dll>, and if I try References.Remove then it fails with error -2147312566 Error in loading dll. If I try and add the correct reference anyway, it gives Error 32813 Name conflicts with existing module, project or library because of the existing missing reference.

Why won't it let me remove the missing reference? I can do it manually, but I need to do it programatically.
0
 
sir plusSales ManagementAuthor Commented:
Hi Chris
"Does the actual DLL exist for sure?" Yes
Though the content of the dll are being recompiled after the reference was created.

"We can perhaps try adding it again"
Already tried that no methods work
No error is returned
Its almost like if you add another reference to the same dll with an existing (but broken) then the "add" does not actually do anything.

To fix it you have to remove the old reference but you cant do that programatically if the reference is "missing".

 ... dunno if it will help but trying out the range of techniques available to us.  If so what is the GUID for the ref?"
GUID method fails too

The problem here is not to do with the name of the DLL file
If the name is changed then the code works to handle that error

The problem occurs if the content of the dll project is changed before it is compiled then all the properties and methods relating to that file become null & void once the excel file s opened with a reference to a dll with changed content then the reference becomes "missing". Once this happens
theRef.IsBroken
theRef.Name
and most importantly
ThisWorkbook.VBProject.References.Remove theRef
All fail

My work around is to save workbook with no reference (delete it with .onsave event) and then create the reference every time it opens so there is no reference to go "missing".

What I need is
"Why won't it let me remove the missing reference? I can do it manually, but I need to do it programatically. "

If no one can help me remove the "missing" (but changed not really missing) reference programatically then I will have to use my work around and will close this question with a rating of C.

0
 
sir plusSales ManagementAuthor Commented:
Ok
I may be onto something
There is a difference between "unchecking a reference in the list" and removing the reference from the list
I have a bunch of references unchecked but still in the list that I dont want to be there.
How do I remove these from the list?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I have some code somewhere for checking / unchecking ... I think.  Let me have a 'pootle' and i'll get back.

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Couldn't find but recreated something like it here:

Enable / disable is via the GUID, a function is presented "ListReferences" so you can establish what 'it' is and then plug it into the code shown in "TestRefRemove" and "TestRefAdd".

Chris
Sub TestRefRemove()
Dim strTheRef As String

    strTheRef = "{E64169B3-3592-47D2-816E-602C5C13F328}"
    If findReference(strTheRef) Then
        RemoveReferenceByGUID strTheRef
    End If
    
End Sub

Sub TestRefAdd()
Dim strTheRef As String

    strTheRef = "{E64169B3-3592-47D2-816E-602C5C13F328}"
    Application.VBE.ActiveVBProject.References.AddFromGuid strTheRef, 0, 0

End Sub

Sub ListReferences()
Dim objRef As Reference

    For Each objRef In Application.VBE.ActiveVBProject.References
        Debug.Print objRef.FullPath, objRef.GUID, objRef.Major, objRef.Minor
    Next

End Sub

Function RemoveReferenceByGUID(strGUID) As Boolean
Dim objRef As Reference

    RemoveReferenceByGUID = True
    For Each objRef In Application.VBE.ActiveVBProject.References
        If objRef.GUID = strGUID Then
            Application.VBE.ActiveVBProject.References.Remove objRef
            RemoveReferenceByGUID = True
            Exit For
        End If
    Next

End Function

Function findReference(strGUID As String)
Dim objRef As Reference

    findReference = False
    For Each objRef In Application.VBE.ActiveVBProject.References
        If objRef.GUID = strGUID Then
            findReference = True
            Exit For
        End If
    Next

End Function

Open in new window

0
 
sir plusSales ManagementAuthor Commented:
Application.VBE.ActiveVBProject.References.Remove objRef
objRef.GUID fails ("Not Available")
objRef.Name ("Not Available")

These properties & methods are not available.

The only thing that works is index but
Application.VBE.ActiveVBProject.References.Remove Reference(x).
Fails too
:-\

Chris
I think I have broken excel finally by finding something I can't do
I have to admit defeat & move on

I will use my work around which I tested & works.
I will delete the reference before it becomes faulty (ie dll file changes) then recreate the reference just before its needed.
If I somehow get a file saved with the faulty link then I will test by trapping that error and if I get that, delete the file and replace same with one with no link.
Not eloquent but works......  

Thanks for the code, and your help is much appreciated. It's nice and neat only there's nothing new (it works fine on other references). I have tried with the GUID already.

If there are no objections I will close this unresolved.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
thats fine by me
0
 
sir plusSales ManagementAuthor Commented:
OK
I cant solve this

The GUID changes when I overwrite the file
excel can't handle this when it sees the file is there but the GUID id different it causes an error because it tries to load it.
I get teh message there was an error loading the library

I have a better work around than the above.

I just wont overwrite the dll
I will create a new one with a different name then the excel sheet will check for the presence of a more recent file and if it exists, delete the old reference and create a reference to the new.
The only thing is each time I do this I am left with a surplus entry in the library list.

 Surplus entries in the library list
How can I clean up the list of references in excel?
(I include this on this thread because it will form part of the solution)

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I think this will be the same issue from the VBA perspective ... but let me look at in later to be sure in my own mind.

Chris
0
 
sir plusSales ManagementAuthor Commented:
Hmm
So if I can't clean that up then it looks like I will have to issue a library for each user and when I update their excel file then update the dll at the same time.

This would be much better if I can pre set the reference to just be AirPack.dll located in the same folder as the workbook

I have lots of users so I could just issue a workboook with a reference already set to the dll in the same folder as the workbook.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
D you know the how and when it is missing ... For someone else we e ded up with a solution that deleted the reference when closing the app and added it again on opn.  Might that work?

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Note the key (potential) difference here is to remove it before it is missing hence if deletion on close or even open works reliably then that might be preferable

Chris
0
 
sir plusSales ManagementAuthor Commented:
That was my work around

"The GUID changes when I overwrite the file
excel can't handle this when it sees the file is there but the GUID id different it causes an error because it tries to load it.
I get teh message there was an error loading the library"

"my work around which I tested & works.
I will delete the reference before it becomes faulty (ie dll file changes) then recreate the reference just before its needed.
If I somehow get a file saved with the faulty link then I will test by trapping that error and if I get that, delete the file and replace same with one with no link.
Not eloquent but works......   "

I guess that will resolve the messy list issue too....


0
 
sir plusSales ManagementAuthor Commented:

Here is the code I use to crreate the ref on workbook opening and deleting it before save

as the guid changes I have to use the name

also i wanted to add the reference after the save was complete so I could keep working on it only theres no native aftersave event so I had to work around that as well

all in all it works well


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    RemoveReference "AirPack.dll"
    Application.OnTime Now, "'AddAReferenceBy_FileOrGUID True, ""AirPack.dll""'"
End Sub

Private Sub Workbook_Open()
    AddAReferenceBy_FileOrGUID True, "AirPack.dll"
End Sub

Open in new window

0
 
sir plusSales ManagementAuthor Commented:
This my work around and the question of how to remove the emissing reference to a library that was not missing was not resolved was not resolved.

Chris B & my solutions were same and arrived at seperately so I am awarding him half the points. If the list could be cleaned up after the dodgy references are removed then it will be an acceptable good solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.