Understanding User-Defined Functions

The defining characteristic of user-defined functions is that they return a result. They can perform any number of calculations on numbers, text, logical values, or whatever, but they generally don't affect their surroundings. In a worksheet, for example, they usually don't move the active cell, format a range, or change the workspace settings.

So, what can you put in a user-defined function? Most user-defined functions consist of one or more expressions. An expression is some combination of values (such as numbers), operators (such as + and *), variables, VBA functions, or application functions that, together, produce a result.

All user-defined functions have the same basic structure:

Function ProcedureName (argument1, argument2, ...)
	[VBA statements]
	ProcedureName = returnValue
End Function

Here's a summary of the various parts of a user-defined function:

Function
This keyword identifies the procedure as a user-defined function. The Function keyword is the reason that user-defined functions also are also known as function procedures.

ProcedureName
This is a unique name for the function.

argument1, argument2, ...
Just as many application functions accept arguments, so do user-defined functions. Arguments (or parameters, as they are sometimes called) are typically one or more values that the function uses as the raw material for its calculations. You always enter arguments between parentheses after the function name, and you separate multiple arguments with commas. (If the function doesn't require arguments, you still need to include the parentheses after the function name.)

VBA statements
This is the code that actually performs the calculations, and it's usually a series of VBA statements and expressions that lead toward an overall result for the function.

returnValue
This is the final result calculated by the function.

End Function
These keywords indicate the end of the function.

All your user defined functions will have this basic structure, so you need to keep three things in mind when designing these kinds of macros:

• What arguments will the function take?
• What expressions will you use within the function?
• What value will be returned?