Excel® VBA: 24-Hour Trainer

Chapter 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 ...