Excel 2010 Bible

Part VI: Programming Excel with VBA

824

An Introductory Example

Creating custom functions is relatively easy after you understand VBA. Without further ado, here’s

an example of a VBA function procedure. This function is stored in a VBA module, which is acces-

sible from the VB Editor.

A custom function

This example function, named NumSign, uses one argument. The function returns a text string of

Positive if its argument is greater than 0 (zero), Negative if the argument is less than 0, and

Zero if the argument is equal to 0. The function is shown in Figure 40.1.

FIGURE 40.1

A simple custom worksheet function.

You can, of course, accomplish the same effect with the following worksheet formula, which uses

a nested IF function:

=IF(A1=0,”Zero”,IF(A1>0,”Positive”,”Negative”))

Many would agree that the custom function solution is easier to understand and to edit than the

worksheet formula.

Using the function in a worksheet

When you enter a formula that uses the NumSign function, Excel executes the function to get the

result. This custom function works just like any built-in worksheet function. You can insert it in a

formula by choosing Formulas ➪ Function Library ➪ Function Wizard, which displays the Insert

Function dialog box. (Custom functions are listed in the User Defined category.) When you select