Making a Reference stick

[Becks]

Hi all

I have created an Excel add-in that is meant to add a Reference of itself to a target workbook. After execution, the target shows the Reference and functions called from the add-in are able to lose the filename descriptor. If you try to close the add-in before closing the target workbook, Excel comes up with the message that the add-in referenced and cannot be closed. All good so far.

However, after saving and closing the target workbook, then closing the add-in, when I open the target workbook, the Reference no longer exists
What am I missing? I thought that the Reference should be saved and that if I re-opened the target workbook, the add-in would also be opened if it was not already open.


Code to create the reference

Code: Select all

Sub AddReference()
Dim i As Integer
Dim bReferenced As Boolean
Dim sMyName     As String

    sMyName = ThisWorkbook.VBProject.Name
    
    With ActiveWorkbook.VBProject
       For i = 1 To .References.Count
           bReferenced = StrComp(sMyName, .References(i).Name, vbTextCompare) = 0
           If bReferenced Then Exit For
       Next i
    End With
    
    If Not bReferenced Then
        ActiveWorkbook.VBProject.References.AddFromFile ThisWorkbook.Name
    End If
End Sub

Regards
Becks

[HansV]

Have you made sure that the add-in has a unique VBA project name? I tested your code and the reference sticks after closing and reopening Excel.