Excel 2016 Formulas

Chapter 25 Function Procedure Basics

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 category
• Tips for testing and debugging functions
• Creating an add-in to hold your custom functions

Previous chapters in this book examined 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 VBA Function procedures, 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 2016 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 ...