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

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

Solution

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