Excel's Insert Function dialog box is a handy tool. When creating a worksheet formula, this tool lets you select a particular worksheet function from a list of functions (see Figure 10-6). These functions are grouped into various categories to make it easier to locate a particular function. The Insert Function dialog box also displays your custom worksheet functions and prompts you for a function's arguments.
Custom Function procedures defined with the Private keyword do not appear in the Paste Function dialog box (although they can still be entered into formulas manually). If you develop a function for exclusive use of your other VBA procedures,you should declare it by using the Private keyword.
By default, custom functions are listed under the User Defined category, but you can have them appear under a different category if you like. You also can add some text to describe the function. (I highly recommend this step.)
In the Insert Function dialog box, notice that the workbook name is also displayed for functions that are defined in a workbook other than the active workbook.
In versions prior to Excel 2002, the Insert Function dialog box was known as the Paste Function dialog box. This dialog box was enhanced in Excel 2002 and now offers the ability to search for a function by keyword.Unfortunately, this search feature cannot be used to locate custom functions created in VBA.
Was this article helpful?