Prevent Excel from saving UDF path to formula cells

Question

I'm using a custom Add-in which adds functions to Excel, like UDF1(param1, param2).

(1) example call in cell: =UDF1(param1, param2)

When saving workbook1, which implements UDF(), Excel silently adds the path to the Add-in to each call of UDF(). This can be seen when uninstalling the Add-in and reopening workbook1.

Excel asks to update external references, I click "don't update".

(2) new cell content: ='\...\user\Desktop\Addin1'!UDF1(param1, param2)

How to prevent Excel from saving the path to the Add-in when saving the workbook?

I would like the new cell content (2) to match the original content (1).

(This is particularly annoying when different people are using the same Add-in, respectively installed in a different location of their computers, as it requires deleting the '\......!' path from each cell each time the workbook is opened by another user.)

Edit: Remove AddIn path from UDF in Excel formula and http://www.jkp-ads.com/Articles/FixLinks2UDF.asp is not what I'm looking for, as the suggested "solutions" are workarounds.

Sulfa

Answer 1

You researched very well the issue*, and you found the answer that you did not like: what you want to do is not possible.

That is the way Excel refers to UDFs (similarly, when you refer to cells in open vs. not-open workbooks).

You need a workaround, either of those proposed in the links you provide, or perhaps something alternative.

.* It is not the typical case.

Mella

Though its always hard to prove the non-existence of a clean solution, you've confirmed what I expected... – Sulfa

@user3641140 - Agreed 100%. I have asked questions in the same spirit before. – Mella

BUT - might it be possible that this is an development environment issue? I.e. there might be a way to solve the problem by creating the Add-In in Visual Studio / C#, instead of the Excel-integrated VBA editor? (I know there exist Add-Ins that don't have the problem described above. After all, isn't the purpose of an Add-In to distribute code...?) – Sulfa

Answer 2

As always, there is a way of using user defined functions (UDF) from an addin. Let us have a function in an excel Addin-File B.xlam in a Module we have

Public Function Volume(a as Double, b as Double, c as Double)
    Volume = a * b * c
End Function

This File has the Workbook.IsAddin-Property set to true and is stored in the same directory as the consuming Excel Sheet A.xlsm. This Excel Sheet has a VBA-Reference to file B.xlam. If You then use the Function Volume in any Cell of A.xlsm, the function is delegated to VBA and VBA also looks, if the Function Volume exists in the referenced File B.xlam. So far so good. If in the workbook-view of Excel, refereces are shown (in the data-ribbon), then change the reference from B.xlam to the file A.xlsm itself. So finally there is no direct Excel-Reference from A.xlsm to B.xlam. The only reference exists is a VBA-Reference.

Now, if the Addin-File B.xlam is loaded before the File A.xlsm the reference always refers to the open file. Otherwise VBA looks for the original path and if that does not exist, it looks in the workbook-path.

So this way the UDF may be referenced via VBA and the addin may sit anywhere, if loaded bevore the consuming file or in the same directory of the consuming Excel-file.

Sheela