Microsoft Office Tutorials and References
In Depth Information
Characteristics of User Defined Functions
characteristics of user defined functions
When used as a worksheet function, the purpose of a UDF is to return a number, string, array, or
Boolean (true or false) value to the cell it occupies. UDFs cannot change the Excel environment in
any way, meaning they cannot place a value in another cell, or change the interior color of any cell
including the cell they are in, or rename a worksheet, or do anything other than return a value to
their own cell.
That said, it’s important to note that a UDF can be called by a macro. This allows the calling
procedure (the macro) to take advantage of the UDF while still retaining the ability to change the Excel
environment. This makes your UDF a versatile tool when integrated with macros.
UDFs cannot be composed by the macro recorder. Although in some cases you can record a macro
and turn it into a UDF by editing the code, most of the time you will create a UDF by writing the
code yourself directly into a standard module.
UDFs are always located in a standard module, though they can neither appear
in, nor be run from, the Macro dialog. UDFs will not work if placed in any other
type of module such as a worksheet, workbook, userform, or class module.
Whichever way the UDF is called, be aware that it will always compile slower than built-in
functions. Avoid reinventing the wheel by using worksheet functions wherever practical, and UDFs for
what worksheet functions cannot do.
Anatomy of a udf
When designing a UDF, it helps to consider three questions:
What is the function’s purpose; that is, what do you want it to accomplish?
What arguments, if any, does the function need?
What will the function return as a formula or provide to its caller in a macro?
A UDF always begins with the Function statement and ends with the End Function statement.
Unless you want your function to be visible only to other code in the same module, it’s best to
declare the function as Public , or omit the Public / Private qualifier altogether, which will default
the function’s scope to Public . Declaring a function as Public will also enable the UDF to be listed
in the Insert Function dialog.
The general syntax of a UDF is:
Function name([argument list]) as type
‘VBA statements that make up the Function
[name = returned expression]