Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 19User-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 hundreds more exist.

Basically, a function performs a calculation or evaluation and then returns a value. Functions used in your VBA expressions act the same way; they do what they are programmed to do and then return a result.

With VBA, you can write (“define”) your own custom function (a user-defined function or UDF) that looks, acts, and feels like a built-in function, but with a lot more power and versatility. After 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 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. You use it 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; to extract only numbers or letters from an alphanumeric string; to place an unchanging random number in a cell; or to test whether a particular worksheet exists or another workbook ...