VBA - where are "available references" stored?

Question

In Excel (or other MS Office apps), when you go to the VBA IDE, the Tools, References list shows you currently selected assemblies as well as a list of others you can add by checkmarking them. You can also hit Browse to navigate to a folder containing a .TLB file of your own making.

But there's a problem. Let's say you you browse to C:\Fubar\PumpHandle.tlb and add it. Everything works fine. A week later, you uncheckmark the reference, because you don't need it in the VBA project your are working on. No problem: PumpHandle is still on the Available References list -- it's just not checkmarked.

A week after that, you delete C:\Fubar\PumpHandle.tlb. Now go into the VBA IDE, Tools, References, and PumpHandle is still on the list, with no way to remove it. Waaaaaah! If you checkmark it, you will of course get an error message, because the PumpHandle.tlb file does not exist. How can you remove it from the Available References list? I have tried searching the Registry and deleting all references to PumpHandle.tlb, but it still shows up in the Available References list. I have tried searching everywhere in %APPDATA%, but I can not find PumpHandle anywhere in there either. Finally, I have looked inside the .xls? --> .zip file of Personal.xlsb, and can't find it in there.

So -- where does the machine store that (obsolete and unusable but highly confusing) reference? How can I get rid of it?

Answer 1

This list is loaded from the registry. The information is put into the registry when you run a command line regsvr32.exe (plain ol' COM) or regasm.exe (.NET COM). This usually happens at installation time and is cleaned up at uninstallation time. Deleting the file won't remove it from the list.

There are a few locations where it could exist in the registry, depending on whether it's a 32-bit or 64-bit type library and upon whether it's registered for all users or only the current user. The list below is (roughly) in order of most common to least common.

• HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib
• HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Classes\TypeLib
• HKEY_CURRENT_USER\Software\Classes\TypeLib

Answer 2

I don't know where they are stored. Maybe this will get you where you want to go, however.

' You need to add a reference to Microsoft Visual Basic for Applications Extensibilty
Sub ZapReference()
Dim VBAEditor As VBE, VBProj As VBProject, VBRef As Reference

    Set VBAEditor = Application.VBE
    Set VBProj = ActiveWorkbook.VBProject
    
    For Each VBRef In VBProj.References
        If VBRef.IsBroken Then
            Debug.Print "Removing: ", VBRef.Name, VBRef.FullPath
            VBProj.References.Remove VBRef
        End If
    Next VBRef
End Sub