XLAM / XLA Addins: is there a better way?

Question

This post is about installing XLAM's without creating links. (Everyone hates links). Consider the trivial addin:

    Public Function UDF_HELLO(x)
        UDF_HELLO = "Hello " & x
    End Function

Put this code and nothing else into a Module and save as "Hello.xlam" on the Desktop (and NOT in the default excel addins folder). Next, while HELLO.XLAM is still open, create a new XLSX workbook with the formula

      =UDF_Hello("world")

in cell A1, which simply displays "Hello world" in that cell. Save the workbook and exit Excel. Now, if you reopen the workbook without the XLAM, Excel will complain about "links to other sources ...". Whether you click "Update" or "Don't Update", Excel will mangle the formula in cell A1 like this:

    ='C:\Documents and Settings\tpascale\Desktop\Hello.xlam'!UDF_Hello("world")

Very often this "forced-linkage" is NOT desirable. In my computing environment there is a lot of ad-hoc analysis and it makes no sense to impose an install regimen on every XLAM we throw together to solve the problem of the day. I just want to hand out XLAM files to users and let those users open them when they need them, WITHOUT having to worry about the slightest mis-step causing their formulas to get mangled.

QUESTION:

Is there a way to instruct Excel to NEVER construct external links for UDFs, and simply to use UDFs if they're loaded and return #VALUEs otherwise ?

Answer 1

I don't know of a way around this with .xla/.xlam add-ins.

But this issue does not occur with .xll add-ins. These can be created in C using the Excel 2010 SDK, or in managed languages like VB.NET or C# using the free Excel-DNA library.

(Disclaimer: I'm the developer of Excel-DNA. This issue is one of the reasons I went with the .xll interface for making managed UDF add-ins.)

Answer 2

You can have them open the .xla file and have an Auto_Open procedure install the add-in.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=693

After excel closes you can have the add-in uninstall itself.

oAddIn.Installed = False

You can give your add-in a setting for the user to not uninstall after every use by using a worksheet named something then have cell A1 equal to true or false.

I haven't tested this but hopefully it works for you.