Dealing with the Insert Function Dialog Box


Excel's Insert Function dialog box is a handy tool. When you are creating a worksheet formula, this tool lets you select a particular worksheet function from a list of functions (see Figure 10-8). 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 the Function Arguments dialog box prompts you for a function's arguments.

image from book
Figure 10-8: Inserting a custom function into a formula.
Note  

Custom Function procedures defined with the Private keyword do not appear in the Insert Function dialog box. If you develop a function that's intended to be used only in your other VBA procedures, you should declare it by using the Private keyword. However, declaring the function as Private does not prevent it from being used in a worksheet formula. It just prevents the function from displaying in the Insert Function dialog box.

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.

Note  

The Insert Function dialog box enables you to search for a function by keyword. Unfortunately, this search feature cannot be used to locate custom functions created in VBA.

Specifying a function category

Oddly, Excel does not provide a direct way to assign a custom function to a category. If you would like your custom function to appear in a function category other than User Defined, you must write and execute some VBA code.

The following statement assigns the function named Commission to the Financial category (category number 1):

 Application.MacroOptions Macro:="Commission", Category:=1 
Note  

You need to execute this statement only one time (not each time the workbook is opened). From then on, every time the workbook is opened, the function will appear in the category that you specified.

Table 10-1 lists the category numbers that you can use. Notice that a few of these categories (10 through 13) are normally not displayed in the Insert Function dialog box. If you assign your function to one of these categories, the category will appear in the dialog box.

Table 10-1: FUNCTION CATEGORIES
Open table as spreadsheet

Category Number

Category Name

All (no specific category)

1

Financial

2

Date & Time

3

Math & Trig

4

Statistical

5

Lookup & Reference

6

Database

7

Text

8

Logical

9

Information

10

Commands

11

Customizing

12

Macro Control

13

DDE/External

14

User Defined

15

Engineering

16

Cube [*]

[*] The Cube category is new to Excel 2007

Adding a function description

When you select a function in the Insert Function dialog box, a brief description of the function appears. You can specify a description for your custom function in two ways: Either use the Macro dialog box or write VBA code.

Note  

If you don't provide a description for your custom function, the Insert Function dialog box displays the following text: No help available.

DESCRIBING YOUR FUNCTION IN THE MACRO DIALOG BOX

Follow these steps to provide a description for a custom function:

  1. Create your function in the VBE.

  2. Activate Excel, making sure that the workbook that contains the function is the active workbook.

  3. Choose Developer image from book Code image from book Macros (or press Alt+F8).

    The Macro dialog box lists available procedures, but your functions will not be in the list.

  4. Type the name of your function in the Macro Name box.

  5. Click the Options button to display the Macro Options dialog box.

  6. Enter the function description in the Description box (see Figure 10-9). The Shortcut Key field is irrelevant for functions.

    image from book
    Figure 10-9: Provide a function description in the Macro Options dialog box.

  7. Click OK and then click Cancel.

After you perform the preceding steps, the Insert Function dialog box displays the description that you entered in Step 6 when the function is selected.

CROSS-REFERENCE  

For information on creating a custom help topic accessible from the Insert Function dialog box, refer to Chapter 24.

When you use the Insert Function dialog box to enter a function, the Function Arguments dialog box is displayed after you click OK. For built-in functions, the Function Arguments dialog box displays a description for each of the function's arguments. Unfortunately, you cannot provide such descriptions for custom function arguments.

DESCRIBING YOUR FUNCTION WITH VBA CODE

Another way to provide a description for a custom function is to write VBA code. The following statement assigns a description for the function named Commission :

 Application.MacroOptions _      Macro:= "Commission", _      Description:= "Calculates sales commissions" 

You need to execute this statement only one time (not each time the workbook is opened).




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net