CODEWITHSUNDEEP
vbaexcel
tpascale
tpascale

Reputation: 2576

XLAM / XLA Addins: is there a better way?

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 ?

Upvotes: 7

Views: 14012

Answers (4)

Lun
Lun

Reputation: 428

I usually solve this problem by:

  1. Saving an XLA/XLAM file (outside Personal folder, of course)
  2. Connect to it in Tools - Addins
  3. Write pseudo macros in your current Excel file that links to those macros / functions in the XLA/XLAM file.

See the detailed instructions in my reply here.

Upvotes: 0

JS20'07'11
JS20'07'11

Reputation: 319

This should work to resolve your issue though it does not instruct Excel regarding external links. I have tested it myself by creating the XLAM, saving it to my desktop, installing it in the Excel add-ins and then using it on a new workbook.

Steps:

  1. Once you have saved the add-in, close it.
  2. Go to Excel Options-->Add-Ins
  3. In the Manage drop-down select Excel Add-ins and press 'Go'
  4. In the 'Add-Ins'dialogue that appears click 'Browse' and navigate to the add-in you just created. Select it and hit 'Ok'
  5. If prompted to save the add-in in the add-ins folder, select 'No'. Selecting 'Yes' may cause an error if the add-in file suffix does not match the version of Excel being used.
  6. Your add-in should appear in the 'Add-Ins available' scrollbox, check its box and hit 'Ok'
  7. Your add-in should now be active whenever you open Excel.
  8. Test this by opening a new workbook and try using your UDF.

Best,

Upvotes: 1

Jon49
Jon49

Reputation: 4606

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.

Upvotes: 1

Govert
Govert

Reputation: 16907

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.)

Upvotes: 4

Related Questions