Microsoft Office Tutorials and References
In Depth Information
Why Create Custom Functions?
In fact, you can use a Function procedure anywhere that you can use an Excel worksheet
function or a VBA built-in function. As far as I know, the only exception is that you can’t use a VBA
function in a data validation formula.
I cover Sub procedures in the preceding chapter, and in this chapter, I discuss Function
procedures.
Chapter 11 has many useful and practical examples of Function procedures. You can
incorporate many of these techniques into your work.
Why Create Custom Functions?
You’re undoubtedly familiar with Excel worksheet functions; even novices know how to use the
most common worksheet functions, such as SUM, AVERAGE, and IF. Excel 2010 includes more
than 400 predefined worksheet functions that you can use in formulas. If that’s not enough,
however, you can create custom functions by using VBA.
With all the functions available in Excel and VBA, you might wonder why you’d 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, coworkers 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 isn’t
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.
An Introductory Function Example
Without further ado, this section presents an example of a VBA Function procedure.
 
Search JabSto ::




Custom Search