Saving a UDF

[Brian Tozer]

I am perusing tutorials describing how to create a Custom Function.
They describe how to create a module and enter the text for the function.
But no mention is made of how or when it is saved.

Also assuming that it is saved somehow in my default workbook, how do I
ensure that it will be available with other already created workbooks?

Thanks
Brian Tozer

 

[Peo Sjoblom]

Brian,

if you want a UDF for one workbook only, just put it in the workbook where
you are using it. If you want access to it always, put it in an empty
workbook and save the workbook as
an add-in *.xla. I believe Excel puts it in the correct directory when you
select the right extension/type from the
dropdown in the save as dialogue box. Then check it under tools>add-ins and
when you restart it should be
available..

http://www.mvps.org/dmcritchie/excel/formula.htm#addin

 

[Gord Dibben]

Brian

1. UDF's and Macros can be stored in a workbook so they are available only
for that workbook.

2. They can be stored in a Personal Macro Workbook that is placed in the
XLSTART folder so they are available for all open workbooks.

3. They can be stored in a workbook which has been saved as an Add-in with
the extension *.XLA

I prefer putting my macros/functions into a personal Add-in(*.XLA) and having
that loaded whenever Excel is opened.

The reason I favor the Add-in route is that you rarely have a problem with
Excel finding the macros/functions when referred to by name only, without
preceding with Personal.xls.!

One minor problem with an Add-in is that the macros don't show up in the
Tools>Macro>Macros list.

The UDF's you write will be available in the Function Wizard in
"User Defined" category.

If you have no Personal.xls one can be built easily.

On menu go to Tools>Macro>Record New Macro.

You will be presented with a dialog box with Macro1 as name of macro. Below
that is "Store macro in". Select Personal Macro Workbook.

Record something simple like copying a cell and pasting. Stop Recording.
Close Excel and you will be asked if you want to save changes to Personal.xls

Click Yes. Excel will save it to your XLSTART folder.

You now have a Personal.xls which will open each time you start
Excel. When it is open, you can go to Windows>Hide and make it hidden. Save
changes again. The macros you create will be available to work in all
workbooks.

At any time Personal.xls can be saved as an Add-in through File>Save
As>Type>Excel Add-in(*.xla) and stored in the Office\Library folder. It will
then show up in the list in Tools>Add-ins to be checked.

Gord Dibben XL2002