Microsoft Office Tutorials and References
In Depth Information
Lesson 16: User Defined Functions
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.