How to connect Excel add-in via references?

Question

Macros

There is an Excel add-in (an .xlam file with some user-defined function). And there is a regular macro-enabled Excel workbook (.xlsm). In the code editor window, I connect this add-on to this file via Tools->References. The add-in appears in the file tree on the left, the function from this add-in works successfully in the workbook. We save the book, close it, open it - the add-in disappears, the user function, obviously, does not work.

What's wrong? I looked in the Excel options, there are some points about trusted sources of add-ins, etc., but I did not see a direct connection.

Details: Excel 2016; the add-on file is located on a network drive; connecting the add-on through Developer->Add-ons is not an option, because the file is supposed to be used by a wide range of users, including non-specialists (do not run around showing everyone how and where to connect the add-on); writing a custom function directly in the book is also not an option, since it is planned to use it in several files, and, if necessary, edit it centrally (I edited the function in the add-in - everything has changed in all files where it is connected), as well as add some new functions.

Answer 1

When Excel is loaded, connected add-ins are also loaded. In your case, if the add-in is not available (the network is not working, the file is already in use, etc.), then Excel will disable the add-in and you can only enable it manually.
Possible solutions:
1. Copying the current version of the add-in by the script to the local folder of Excel add-ins;
2. When opening Excel - fautorun macro that connects the add-in from a network folder;
3. Use of other types of add-ons where the network deployment is already "bundled". For example VSTO.