Sharing XLAM Add-In with other users...?

[justhumm]

I am not too familiar with Excel add-ins, but I have a simple XLAM add-in that has a few different User Defined Functions (UDFs).

I've shared this XLAM with a few people. And, when they open up a workbook where I inserted a UDF, even when they have the add-in installed and activated, Excel is inserting a path to the add-in directory on my computer.

Just out of curiosity, why is Excel inserting a filepath to the original user's add-in folder, as opposed to just realizing that the UDF is defined in Excel's default UDF folder?

And, what I'm more interested in learning, is there some sort of workaround for this, so that Excel stops inserting the file path when multiple users are working with the UDF-containing file?

Thanks!

 

[combo]

Excel xlam add-in is a regular workbook with xlam extension, marked as add-in, thanks to this: it is (1) hidden in UI, (2) fires additional add-in Install/UnInstall events, (3) its functions are visible as a part of excel functions, (4) in VBE one can see it, if installed, in Projects window (as Solver for instance).
Installed excel add-in is ticked in excel add-ins dialog.

If excel add-in is installed, when a workbook referring to its functions is open, its path shouldn't be visible in formula bar. It may happen if the user opens the add-in instead of installing it, or formulas in target workbook were used before the add-in was installed - they refer to add-in as external workbook.

To fix it:
- the add-in should be installed and ticked on all computers,
- open the file with UDF after installing add-in,
- in any computer the add-in UDF should be displayed without path, if not - fix it,
- save file, distributed should refer to local add-in.


combo

 

[justhumm]

Thanks for the reply, combo, but no banannas.

What you described is the behavior that I would expect to get from Excel, but Excel is still inserting

This morning, I had a Teams call with a coworker. We checked that person's computer to make sure we had the same XLAM file on our local computers. They opened up a spreadsheet that I created (with UDFs). We fixed all of the UDF references. And they closed the file down.

Then I opened the file on my computer and the path to my coworker's folder was present in all of the UDF cells.

Any thoughts on what might be causing this?

The XLAM file has a class module and event handler in it. Other than that, it looks like a pretty normal Excel file. Would the class module effect the behavior, at all?

Happy Friday!

 

[strongm]

It's a 'feature' ... basically as soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors, and the complete path to the original location of the add-in is shown in each formula. Basically, excel is not designed to 'just realiz[e] that the UDF is defined in Excel's default UDF folder?". Thing is that XLAMs were really designed to provide a common library for a single user, not as a shared library for multiple users (hence the default location) - although you can make it work that way. You just need to put the XLAM in a folder that has the same path on all machines. e.g. a network share that everyone can map to, or perhaps create an XLAM folder under C:\Users\All Users