User Defined Functions

Maybe your formulas are just getting too long and too complicated.
Long and complicated formulas can be very difficult to understand.
You need to be comfortable familiar with Macros to write you own user defined functions.
This is an example of a user defined function, very similar to the built-in UPPER.
This function returns a given character as a capital letter.
Remember to enter your subroutine as a Function and not as a procedure (i.e. Sub).

Public Function BET_CapitalLetter(ByVal sChar As String) As String 
   If (Asc(sChar) >= 97) And (Asc(sChar) <= 122) Then 
      BET_CapitalLetter = Chr(Asc(sChar) - 32) 
   Else 
      BET_CapitalLetter = sChar 
   End If 
End Function 

Ensure they are declared as Public and written in an actual module as opposed to a worksheet module.
When creating functions they must be placed in a normal VBA module and not a worksheet code module.
Any user defined function will be added to the User Defined category by default but can be added to a specific function category if you want.

Differences between a Macro and a User Defined Function

A Macro allows you to execute a sequence of simple commands where as a function allows you to perform a sequence of calculations and then return a result.
A User Defined Function cannot be recorded using the Macro Recorder.
User defined Functions are designed to perform calculations and return a value. They are not to be used in place of macros.
They cannot change the contents of any other cells (only the active cell).
A Macro does not require any arguments passed into it where as a function normally takes at least one argument.

Saving my User Defined Function

There are a number of ways you can save and access your user defined functions:
(1) Inside the Active workbook.
(2) Inside a Different workbook.
(3) Inside your Personal.xlsb
(4) Inside an Excel add-in.
All these different scenarios are described below.
We will be using the WORKSHEETNAME function as an example.

1 - Inside the Active Workbook

The quickest way to add and use a user defined function is just to put the code into the corresponding workbook.
When user defined functions are put into the actual workbook you can access it just by using the name of the function.

The major disadvantage is that none of your other workbooks can access this function.
However this is the best approach if there is a chance the workbook will be emailed to someone else.

2 - Inside a Different Workbook

It is possible to use a user defined function that has been defined in another workbook.
The only draw back of this approach is that the other workbook must be open for this function to work.

You can save yourself from prefixing the user defined function with the name of the workbook by creating a permanent reference to the other workbook.
This can only be done from within the Visual Basic Editor.
Before you add the reference though you need to change the VBA Project name of the workbook that contains your functions.
Select the relevant project in the VBA Editor and select (Tools > VBAProject Properties).
Change the project name to something you will more meaningful which you will remember and press OK.

Select the project for the other workbook, i.e. the one you want to be able to use the functions in.
Select (Tools > References) and find your entry in the References dialog box.
Make sure this entry is ticked and press OK.

This will create a reference from your workbook to the workbook that contains the user defined functions.
You will see the corresponding entry added to the References node of that particular project.
Once this reference has been set up you can access it just by using the name of the function.

3 - Inside your Personal.xlsb

4 - Inside an Excel Add-in

If the function has been saved inside an Excel add-in which is currently loaded then you can type the name of the function directly into a workbook as you would any other function.
For more information regarding add-ins please refer to the Creating an Excel Add-in page.

Problems and Errors

If a workbook contains a user defined function which cannot be found then #NAME? is displayed in the cell.
When you select this cell you will be able to see a "full" reference to the location of the user defined function the last time the workbook was saved.
If you are using an add-in and the user defined function is not being recognised prefix it with the full folder path.
Sometimes when you create and use a custom worksheet function it will appear in lowercase, sometimes mixed case and sometimes upper case.
It all depends on which case is used the first time you use the custom worksheet function.

Custom Worksheet Functions are inconsistent

Sometimes these functions are in lowercase, sometimes uppercase and sometimes mixed case.
The case that will be used seems to be determined by the case used the very first time the function is called from Excel.

MMULT and SUMPRODUCT - handle all the array computations so it is not necessary to worry about the specific dimensions of the array in the code or to process individual elements

Limitations

Function names must adhere to certain rules for example you can't use a name that resembles a cell address, for example B22.
They cannot copy and paste cells to other parts of a worksheet.
They cannot change the active worksheet.
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.
They cannot open additional workbooks.
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.

Important

Remember that functions will not appear in the (Tools > Macro > Macros) dialog box.
Your functions will only appear in the (Insert > Function) dialog box if they have been declared Public (this is the default if not specified).
Any functions declared as Private will not appear in the (Insert > Function) dialog box.
You should declare all your user defined functions as Public and any other subroutines or functions as Private.
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 (.xlam).
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).
Use Excel worksheet functions where possible as these are always faster than equivalent VBA functions.
It is not possible to add individual descriptions to each argument - or is it ??
It is possible to display help for arguments of a custom function in the Paste Function dialog box. This can be done using the Object Browser and will be effective even if you convert the workbook to an add-in.
Any custom worksheet functions that you write must be strictly "passive" and cannot change the active cell or apply formatting.
To return a real error from a function, use CVErr() which converts an error number into the real error. There are built in constants for the common worksheet errors.
You can write custom functions that return arrays (i.e. as in array formulas).
You cannot include full stops in your VBA Project Names.
You cannot create a reference to an unsaved workbook.