Excel 2010 Bible

Part VI: Programming Excel with VBA

834

l (^) Test the procedure by calling it from a Sub procedure. Run-time errors display nor-
mally, and you can either fix the problem (if you know what it is) or jump right into the
debugger.
l Set a breakpoint in the function and then use the Excel debugger to step through the
function. Press F9, and the statement at the cursor becomes a breakpoint. The code will
stop executing, and you can step through the code line by line (by pressing F8). Consult
the Help system for more information about using VBA debugging tools.

Inserting Custom Functions

The Excel Insert Function dialog box is a handy tool from which you can choose a worksheet func-

tion; you even can choose one of your custom worksheet functions. After you select a function, the

Function Arguments dialog box prompts you for the function’s arguments.

Note
Function procedures that are defined with the Private keyword do not appear in the Insert Function dialog
box. So, if you create a function that will be used only by other VBA procedures, you should declare the func-
tion by using the Private keyword. n

You also can display a description of your custom function in the Insert Function dialog box. To

do so, follow these steps:

1. Create the function in a module by using the VB Editor.


2. Activate Excel.


3. Choose Developer ➪ Code ➪ Macros. Excel displays its Macro dialog box.


4. In the Macro dialog box, type the name of the function (in the Macro Name field).
   Notice that functions don’t typically appear in this dialog box, so you must enter the
   function name yourself.


5. Click the Options button. Excel displays its Macro Options dialog box. (See
   Figure 40.3.)


6. Enter a description of the function and then click OK. The Shortcut key field is
   irrelevant for functions.

The description that you enter appears in the Insert Function dialog box.