Excel add-in install gives a subscript out of range error on initial install

Question

I have some code that when the workbook opens, checks to see if an add-in is there, and depending on the scenario, downloads a copy from onedrive.

My issue is that on the initial running, it errors with a subscript out of range on the last add-in install line. If the user closes excel and reopens, then it installs without issue. Stranger, if I have them delete the add-in from the add-ins folder, it still installs fine when they open the workbook. It's always the initial running that flags the error. For now I've been putting an on error handler that instructs them to re-open excel when the initial install error triggers. Any ideas?

sUserName = Environ("username")

sSourceAddInPath = "C:\Users\" & sUserName & "\xxxxxxxxxx\Excel\MyAddIn.xlam"

sDestinationAddInPath = Application.UserLibraryPath & "MyAddin.xlam"

'Add-in exists
If Dir(sDestinationAddInPath) <> "" Then

  'Update if newer version available
  If FileDateTime(sSourceAddInPath) > FileDateTime(sDestinationAddInPath) Then

    FileCopy sSourceAddInPath, sDestinationAddInPath

  End If

'No Add-in 
Else

  FileCopy sSourceAddInPath, sDestinationAddInPath

End If

AddIns("MyAddIn").Installed = True

[Seems related](https://stackoverflow.com/a/56384692/9245853), — BigBen, Aug 26 '21 at 19:37
I saw that, but it doesn't appear to be the same issue. It always works after the initial running of the workbook. It's the very first time that causes the problem. — imrogfar, Aug 26 '21 at 20:31
I'd reuse the `Listed` function from the linked thread here. — BigBen, Aug 26 '21 at 20:32
I'll give that a try and see if it solves the issue. I don't want to mark as a solution yet, so I guess I have to leave open. — imrogfar, Aug 27 '21 at 05:37
Not sure I correctly understand your question. If the add-in in discussion **is installed**, it should be open. In such a case, how to copy overwriting an open file? When use `FileCopy sSourceAddInPath, sDestinationAddInPath`, I mean... Do you/the user firstly uncheck the add-in from 'Add-inns' list? Is it something I am missing? — FaneDuru, Aug 27 '21 at 07:19
I'll repeat it. When a user would FIRST open the workbook(with the code above) that connects to download and install the add-in, on the INITIAL running, it would always fail at the install line. The add-in was downloaded to their add-ins folder(I had them look to see), but the install line couldn't see it was there. If they close and re-open, then it sees it and installs fine. The code listed above is the only thing ran on their end — imrogfar, Aug 27 '21 at 20:03

imrogfar · Accepted Answer · 2021-09-01T22:37:21.607

I solved it by changing the code and using the Add method.

Addins.Add sSourceAddInPath

From testing, it appears I don't need to test and copy the add-in file locally. The Add method will create a registry entry linked to the add-in stored in the user's local onedrive folder, which is also linked to the sharepoint site. This way, when I update the file on sharepoint, it updates their onedrive version.

I also added a helper function stored in the opening workbook to test if the add-in was loaded(i.e., in the list of add-ins) so as to not have to call Add each time the workbook is opened. Not sure if that matters, to be honest.

Function AddInIsLoaded(sAddInName as String) As Boolean
Dim aiAddIn as AddIn
Dim bLoaded as Boolean

bLoaded = False

On Error Resume Next
'Will error if add-in not loaded
Set aiAddIn = AddIns(sAddInName)

If Err.Number = 0 Then
  bLoaded = True
End If

AddInIsLoaded = bLoaded
End Function

Updated code:

sUserName = Environ("username")

sSourceAddInPath = "C:\Users\" & sUserName & "\xxxxxxxxxx\Excel\TheAddIn.xlam"
 
If Not AddInIsLoaded("TheAddInName") Then   
  AddIns.Add sSourceAddInPath
End If    

AddIns("TheAddInName").Installed = True

Excel add-in install gives a subscript out of range error on initial install

1 Answers1