Microsoft Office Tutorials and References

In Depth Information

**Lesson 16: User Defined Functions**

16

user Defined Functions

Most Excel users who are not absolute beginners use worksheet functions in their formulas.

The most common worksheet function is the
SUM
function, and there are hundreds more.

Basically, a function performs a calculation or evaluation, and returns a value. Functions used

in your VBA expressions act the same way; they do what they are programmed to do, and

return a result.

With VBA, you can write (“define”) your own custom function that looks, acts, and feels like

a built-in function, but with a lot more power and versatility. Once you get the hang of UDFs,

you’ll wonder how you ever got along without them.

WHAT is A usEr dEfinEd funcTion?

You are already familiar with many of Excel’s built-in worksheet functions such as
SUM
,

AVERAGE
, and
VLOOKUP
, but sometimes you will need to perform calculations or get

information that none of Excel’s built-in functions can accomplish. A User Defined Function (UDF) is

a function in VBA that you create with arguments you specify, to use as a worksheet function

or as part of a macro procedure, when a task is otherwise impossible or too cumbersome to

achieve with Excel’s built-in formulas and functions.

For example, you may need a formula to sum a range of numbers depending on a cell’s

interior color; or to extract only numbers or letters from an alphanumeric string; or to place an

unchanging random number in a cell; or to test whether a particular worksheet exists or

another workbook is open. UDFs are an excellent option for handling tasks when regular

worksheet functions cannot or should not be used.