Excel User-defined Functions accross multiple users

Question

What is the best way to set-up user-defined functions to be shared accross users in a small working group.  We have tries creating an identical XLA established as an "add-in" on each user profile.  Excel files tend to save the user specific path in the file with the function name requiring massive search and replace operations before begining work. 

What is the work around for this.  Running Excel as part of Office 2007.

Answer 1

Here is one way to avoid "massive search and replace operations" in Excel 2007.

If the XLA file has been opened, when you open a workbook containing references ("links") to the UDFs, Excel should show a dialog box with a warning to update links. Instead, if you see a security warning that "automatic update of links has been disabled," choose to "enable this content," and click OK. Excel will show a dialog box where you should choose "Edit Links..." In the Edit Links dialog box, choose Change Source, and navigate to the location of the XLA file, select it, click Open, and Close the Edit Links dialog box.

If the XLA file is not open, and you open a workbook containing references ("links") to the UDFs, proceed as above, but click "Continue" instead of clicking "Edit Links." After you open the XLA file, choose Office Button > Prepare > Edit Links to Files, and change the source.

Another way to avoid search-and-replace is to save the XLA file in exactly the same folder (same path) on all computers.

-  Mike Middleton, www.TreePlan.com

Answer 2

Can you elaborate on "Excel files tend to save the user specific path in the file with the function name "

I would give everyone the same XLAM file and have each of them place it in their XLSTART folder. Then they should use File|Options to add this add-in.

Or have them paste all the code into a module in their PERSONAL.XLSB file

The simplest way to locate a function in either of these places is to use the Function Dialog (fx thingy on the formula bar) and specify User Defined in the "Or select a category" box.

best wishes