Leading the way in Microsoft Office Development

What is a User Defined Function ?

There will be times when it is appropriate for you to create a custom worksheet function.

You can easily create your own custom functions to be used within your worksheets.

A custom function is basically a worksheet functions perform a mathematical operation using the arguments passed to it.

Custom functions can also be used to significantly shorten your formulas. However custom functions are usually much slower to calculate than built-in functions.

Using VBA code it is possible to create your own functions that can be called directly from a worksheet.

When your custom worksheet function is re-calculated it behaves just like an Excel worksheet function and is only re-calculated when any of its arguments are modified.

Do I need a User Defined Function ?

Maybe your formulas are just getting too long and too complicated.

Long, complicated formulas can be a nightmare to understand especially if they return the wrong answer.

It is recommended that you are familiar with macros and are at least comfortable recording macros before trying to write your own custom functions.

What do I need to consider ?

Decide how many arguments you need to pass to the function

Also spend a bit of time to decide the datatypes and the possible ranges of these arguments.

Do you want to include error handling in your function. You may want to return an error value when certain parameters are entered.

Decide on the datatype that the function will return, whether an Integer, Long etc.

Where is the best place to keep it ?

Where is the best place to store your custom function.

Is your function only relevant to one workbook or would it be useful if was available to all your workbooks.

The big advantage with keeping your functions in an Excel addin is that you will not have to precede the name of the function with the name of the workbook, where as you will if the function is stored in another workbook.

When creating functions they must be placed in a VBA module and not a worksheet code module. Insert array formulas.

These functions are created in Visual Basic. You can write a function that maintains its current value.

Storing them in your Personal.xls – Ensure that they are declared in a separate code module and not in a sheet module.

Differences between a Macro and a Custom Function

A Macro does not require any arguments passed into it where as a custom function probably will.

A Macro uses the keyword "Sub" in its declaration, where as a function uses the keyword "Function".

A Macro allows you to execute a sequence of simple commands where as a function allows you to perform a sequence of calculations.

There are a few limitations

Remember that custom functions are designed to perform calculations and return a value. They are not to be used in place of macros.

Custom worksheet functions do have a few limitations.

1) They cannot change the contents of any other cells (only the active cell).

2) They cannot copy and paste cells to other parts of a worksheet.

3) They cannot change the active worksheet.

4) They cannot apply formatting to the active cell.

If you want to change the cell formatting when different values are entered (or displayed), you should use either Conditional Formatting or use the Change Event method.

5) They cannot open additional workbooks.

6) They cannot be used to run macros the using Application.OnTime.

A custom function can display a message box or input box.

A custom function can make changes to cell comments.

Things to Remember

If you have written a custom worksheet function and do not want to precede the function name with the workbook name then save your workbook as an Excel addin (.xla).

A custom function will usually have one or more "arguments" although it can be used to just return a value.

Custom Functions will always appear in lowercase in your worksheets.

Your custom function named cannot contain spaces but they can contain the underscore character. The preferred method though is to actually use capital letters at the start of each new word (e.g. "CapitalLetter").

Custom Functions are also known as User Defined Functions (or UDF's).

If you are having problems writing your custom worksheet function(s) let us know. If we think that your function will solve a common problem we will write it FREE of charge.