Why Create Custom Functions?

You are undoubtedly familiar with Excel worksheet functions; even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. By my count, Excel contains 340 predefined worksheet functions. If that's not enough, however, you can create custom functions by using VBA.

NEW  

Excel 2007 incorporates all the functions that are included in the Analysis ToolPak add-in.

With all the functions available in Excel and VBA, you might wonder why you would ever need to create new functions. The answer: to simplify your work. With a bit of planning, custom functions are very useful in worksheet formulas and VBA procedures.

Often, for example, you can create a custom function that can significantly shorten your formulas. And shorter formulas are more readable and easier to work with. I should also point out, however, that custom functions used in your formulas are usually much slower than built-in functions. And, of course, the user must enable macros in order to use these functions.

When you create applications, you may notice that some procedures repeat certain calculations. In such cases, consider creating a custom function that performs the calculation. Then you can simply call the function from your procedure. A custom function can eliminate the need for duplicated code, thus reducing errors.

Also, co-workers often can benefit from your specialized functions. And some may be willing to pay you to create custom functions that save them time and work.

Although many cringe at the thought of creating custom worksheet functions, the process is not difficult. In fact, I enjoy creating custom functions. I especially like how my custom functions appear in the Insert Function dialog box along with Excel built-in functions, as if I'm re-engineering the software in some way.

In this chapter, I tell you what you need to know to start creating custom functions, and I provide lots of examples.