0

I have a C# Library which I use in an Excel VBA project. I wish now wish to restructure my project and want to move the library to a different folder. However after removing the reference to the library and deleting all instances of the library from my computer, the References Available box for the project still shows the reference to the old library location even though it no longer exists on the computer and nothing I do seems to be able to remove that reference.

I don't know if this makes any difference, but the project is an Excel AddIn.

I had this problem back in 2019, and received a response to a question with the same title in April 2019. On that occasion the the issue was resolved by following the advise given. However this time the system stubbornly refuses to play ball.

The procedure that I was advised to follow in 2019 was:

  1. Remove the reference
  2. Save the File and close it
  3. Delete the dll (in my case the .tbl) file from your computer. Do not save it anywhere.
  4. ReOpen the file
  5. Check if there is any reference still there. If not copy the dll to a new folder and then set a reference again. Save and close the file.
  6. ReOpen to check if everything is OK

I was going to try code to remove the reference, but the following code did not find the reference

Sub delRef()
    deleteReference ("FiskDLLlib")
End Sub
Sub deleteReference(s As String)
    Dim oFs As Object, oReferences As Object, oReference As Object
    Dim sFileName As String, sRefName As String, sRefFileName As String
    Dim toBeDeletedRef As String
    Set oReferences = Application.Workbooks("fiskAIWkBook.xlam").VBProject.References
    For Each oReference In oReferences
        sRefFileName = oReference.FullPath
        sRefName = oReference.name
        If sRefName = s Then
            toBeDeletedRef = s
            Exit For
        End If
    Next
    
    If toBeDeletedRef <> "" Then
        Debug.Print oReference.FullPath
    Else
        Debug.Print "No Reference found for " & s
    End If
End Sub

Similarly the Watches panel didn't show the library.

I have subsequently discovered a registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib{B22F6C9D-53E0-4D1B-9596-56AA1EA4BDBA}\1.0\0\win32 the value of which was set to the location of the reference I am trying to remove. I changed this to a reference to the new location in which I want to store my dll (.tlb) file. This at least looked like it would allow me to create a new reference in the References box but on clicking OK I got a message saying "Error in loading DLL"

felix
  • 7
  • 4
  • Why are you doing this manually in the first place? why not deploy the add-in using clickonce or other apps? The best way would be to create an installer for your add-in so that it can be uninstalled easliy without you having to do this manually everytime. – Siddharth Rout Aug 08 '20 at 07:42
  • My application is written in VBA making extensive use of the Excel API. It utilises multiple spreadsheets, an Access database and a number of external Libraries (Some which I have developed, others are commercial products). It consists of hundreds of VBA macros and tens of thousands of lines of code and represents a number of man-years of effort. It has been a retirement project for me to assist in management of my financial affairs and investments and has not been developed as a commercial product. At 79 years of age I do not wish to redevelop on another platform. – felix Aug 08 '20 at 20:49
  • [Broken references](https://bettersolutions.com/vba/visual-basic-editor/references-vbaproject.htm) may not return a name, add a check for `.IsBroken`.You used `Regsvr32` to remove dll? – ComputerVersteher Aug 09 '20 at 09:17
  • I can't check the .IsBroken because the reference is no longer in Application.Workbooks("fiskAIWkBook.xlam").VBProject.References . I did not use Regsvr32 to remove dll. I simply deleted the .dll, .pdb and .tlb files from the folder in which they were stored. – felix Aug 09 '20 at 22:06
  • `VBProject.References`doesn't contain the ref because `oReference.name` doesn't show its name? You can't trust that. To use a .Net assembly with a COM-Client (Office) it needs to be registered with [Regasm.exe](https://learn.microsoft.com/de-de/dotnet/framework/tools/regasm-exe-assembly-registration-tool). Try unregister, then re-register your assembly. – ComputerVersteher Aug 10 '20 at 08:24
  • More on [COM registration](https://github.com/rubberduck-vba/Rubberduck/wiki/COM-Registration). Also try the [add-in](https://rubberduckvba.com/) as it provides an extended [reference explorer](https://rubberduckvba.com/Features/Details/ReferenceExplorer). – ComputerVersteher Aug 10 '20 at 09:52
  • What are the correct parameters to regasm to unregister my xxxx.tlb I have tried (1) /u xxxx.tlb (2) /u /t xxxx.tlb (3) /u /t :xxxx.tlb (4) /u xxxx.dll (5) /u "[xxxx.tlb]" (6) /u "[xxxx.dll]" (7) /u "xxxx.tlb" (8) /u "xxxx.dll"? None of these worked. I can't remember how I registered this originally as that was some years ago. – felix Aug 11 '20 at 06:04
  • I've also noted that my registry key now has a subkey (probably due to finger troubles on my part. Originally there was under the [GUID] a subkey "1.0" Now under "1.0" there is a subkey "0" which itself has a subkey "win32" the value of which is a misspelling of the original key value which still remains as the value of the [GUID] – felix Aug 11 '20 at 06:12
  • sorry missed your comments. I would have get notified if your comment contained @computerversteher (see [What happens when I comment?](https://stackoverflow.com/help/privileges/comment)) – ComputerVersteher Aug 21 '20 at 00:25
  • `regasm.exe /u [full path to your DLL in double quotes]"` https://stackoverflow.com/q/7841428/9439330 – ComputerVersteher Aug 21 '20 at 00:35

1 Answers1

0

I resolved my problem in the end by removing all references to the library in the Registry with a registry Cleanup tool (ReImage) before copying the library to its new directory and then reregistering it with regASM

felix
  • 7
  • 4