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