I have had a couple of queries recently which seem to be resulting from trying to run User Defined Functions (UDFs) from a new workbook, so this post will look at the options for using the VBA routines provided here (or anywhere else) with new data.

VBA routines come in two basic types:

• UDFs, which work in a similar way to the built-in Excel functions.  The function name is entered along with the required arguments, and it displays the results, either a single value or a range of values.
• Macros, which perform a series of operations, either predefined, or with interaction from the user.  Macros are initiated either by clicking on a button (or other object), or through a custom menu or icon on  a tool bar or ribbon, or by selecting from a list (press Alt-F8, or click Macros on the Developer Ribbon).

In both cases all the VBA code defining the routines is stored with the workbook they were created in.  Once the workbook is closed, the routines are no longer available.  To use the routines with the new data there are several options:

The quickest and easiest is to simply save the original workbook with a new name, and enter (or copy and paste) in the new data.  UDFs may be used anywhere, just select the required data ranges as you would for a built-in function.  Macros often use named ranges, and data should be entered in the ranges used in the examples provided.

It is possible to use UDFs in a new workbook, providing the original file is kept open.  The screenshot below shows the UDF Cubic() being used in a macro-free workbook, by calling it from the Polynomial workbook:

Calling Cubic from the open file, Polynomial.xlsb

The “Insert Function” dialog is called by clicking the fx icon immediately to the left of the edit bar, then select “User Defined” in the “select a category”.  This will list all UDFs in all open workbooks; for those not in the current active workbook the function name will be preceded by the workbook name.  After selecting the desired function, click OK, and the function arguments may be selected as usual:

Selecting the data range for the Cubic function

The third option is to save the workbook containing the UDF as an add-in, and install it as described here: Creating An XLA Add-In For Excel and  Installing An Add-In

After installing and opening the add-in file all the functions it contains will be listed in the Insert Function dialog, but will also appear as a tool-tip, as you type the function name: