Why Create Custom Functions

You are undoubtedly familiar with Excel's worksheet functions — even Excel novices know how to use common worksheet functions such as SUM, AVERAGE, and IF. By my count, Excel contains more than 300 predefined worksheet functions, not counting those available through add-ins, such as the Analysis ToolPak (which is included with Excel). And if that's not enough, you can create functions by using VBA.

With all the functions available in Excel and VBA, you may wonder why you would ever need to create 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 significantly shorten a formula by creating a custom function. After all, shorter formulas are more readable and easier to work with.

326 Part VI: Putting It All Together

What custom worksheet functions can't do

As you develop custom functions for use in your worksheet formulas, it's important that you understand a key point. VBA worksheet Function procedures are essentially passive. For example, code within a Function procedure cannot manipulate ranges, change formatting, or do many of the other things that are possible with a Sub procedure. An example may help.

It might be useful to create a function that changes the color of text in a cell based on the cell's value. Try as you might, however, you can't write such a function. It always returns an error value.

Just remember this: A function used in a worksheet formula returns a value — it does not perform actions with objects.

0 0

Post a comment