Re: user-defined functions (Excel XP)

You can save a workbook as an add-in using File | Save As...
Microsoft Excel Add-In (*.xla) is the last item in the Save As Type dropdown list.
Excel will automatically suggest to save it in the Add-Ins folder.
You will then have to tick it in Tools | Add-Ins... to activate it.

Using an add-in has two advantages:
- You can easily distribute it to others. In general, you won't want to distribute Personal.xls, since it would overwrite the customizations the recipient had.
- You can use functions just as if they are built-in functions, no need to prefix them with the workbook name.

The downside is that it is a bit more work to create one, but it is not really difficult.

User-defined functions (Excel, all versions)

How to create and use user-defined functions in Excel

If you want the function to be available in all workbooks, you can put it in an Excel add-in or in your personal macro workbook Personal.xls. See <post#=380382>post 380382</post#> in this thread for a very short instruction on how to create an add-in, and see Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> for information about Personal.xls. If the function is meant to be used in a single workbook, put it in that workbook.

In all cases, you create the function in the Visual Basic Editor. You activate it by selecting Tools | Macro | Visual Basic Editor or by pressing Alt+F11. In the Project Explorer, the Windows Explorer-like tree view in the left hand pane, click on the workbook in which you want to create the function, then select Insert | Module. A module is a storage space for Visual Basic code; it can contain multiple macros and functions.

A function will usually take one or more arguments, and it must return one value. Here is a simple example: if you want to convert a temperature from Centigrade (degrees Celsius) to Fahrenheit, you must multiply the temperature in Centigrade by 1.8 and add 32 to the result. Type or copy the following code into the module:

The function takes one argument, Celsius; it is declared as a Variant; that means that we don't tell Visual Basic its exact type; it could be a text string, or a number, or a cell reference. The outcome of the function is also declared as a Variant.

The function first tests if the input value is an empty string "". This is the case if the input is a reference to an empty cell. If so, the result is also an empty string. Otherwise, the input is multiplied by 1.8 and 32 is added, and this is the outcome of the function.

So the result can be a string (empty) or a number; that is why we declare the result as a Variant.

Note: if you pass a non-empty text value to the function, the result will be #VALUE. That is as expected, since we can't convert text to Fahrenheit.