Chapter 27: Creating Custom Functions

Overview

Although Microsoft Office Excel 2007 includes a multitude of built-in worksheet functions, chances are it doesn't have a function for every type of calculation you perform. The designers of Excel couldn't possibly anticipate every calculation need of every user. But they do provide you with the ability to create custom functions. In the same way that a macro lets you encapsulate a sequence of actions and then execute that sequence with a single command, a custom function lets you encapsulate a sequence of calculations so you can perform those calculations with a single formula.

Custom functions, like macros, use the Visual Basic for Applications (VBA) programming language. They differ from macros in two significant ways. First, they use Junction procedures instead of sub procedures. That is, they start with a Function statement instead of a Sub statement and end with End Function instead of End Sub. Second, they perform calculations instead of taking actions. Certain kinds of statements (such as statements that select and format ranges) are excluded from custom functions. In this chapter, you'll learn how to create and use custom functions.