Creating a Simple Custom Function

Using Custom Functions

Understanding Custom Function Rules

Using VBA Keywords in Custom Functions

Documenting Macros and Custom Functions

Creating Custom Functions with Optional Arguments

Making Your Custom Functions Available Anywhere

ALTHOUGH Microsoft Excel 2010 includes a multitude of built-in worksheet functions, chances are that 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 function 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.