Chapter 23: Function Procedure Basics

Previous chapters in this book examined Excel's worksheet functions and how you can use them to build more complex formulas. These functions provide a great deal of flexibility when creating formulas. However, you may encounter situations that call for custom functions. This chapter discusses the reasons why you may want to use custom functions, how you can create a VBA Function procedure, and methods for testing and debugging them.

Why Create Custom Functions?

You are, of course, familiar with Excel's worksheet functions-even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. Excel 2007 includes 340 predefined worksheet functions-everything from ABS to ZTEST.

You can use Visual Basic for Applications (VBA) to create additional worksheet functions, which are known as custom functions or user-defined functions (UDFs). With all the functions that are available in Excel and VBA, you may wonder why you would ever need to create new functions. The answer: to simplify your work and give your formulas more power.

For example, you can create a custom function that can significantly shorten your formulas. Shorter formulas are more readable and easier to work with. However, it's important to understand that custom functions in your formulas are usually much slower than built-in functions. On a fast system, though, the speed difference often goes unnoticed.

The process of creating a custom function is not difficult. In fact, many people (this author included) enjoy creating custom functions. This book provides you with the information that you need to create your own functions. In this and subsequent chapters, you'll find many custom function examples that you can adapt for your own use.