Microsoft Office Tutorials and References
In Depth Information
Chapter 24: Function Pro-
In This Chapter
• Why you may want to create custom functions
• An introductory VBA function example
• About VBA Function procedures
• Using the Insert Function dialog box to add a function description and assign a function
• Tips for testing and debugging functions
• Creating an add-in to hold your custom functions
Previous chapters in this topic examine Excel's built-in 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 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 com-
mon worksheet functions, such as SUM, AVERAGE, and IF. Excel 2013 includes more than 450 predefined
worksheet functions — everything from ABS to ZTEST.
You can use 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 for-
mulas are usually much slower than built-in functions. On a fast system, though, the speed difference often goes
The process of creating a custom function is not difficult. In fact, many people (this author included) enjoy creat-
ing custom functions. This topic 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.