Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

In this section, I discuss some of the technical details that apply to Function procedures. These are general guidelines for declaring functions, naming functions, using custom functions in formulas, and using arguments in custom functions.

Declaring a Function

The official syntax for declaring a function is as follows:

[Public | Private][Static] Function name ([arglist]) [As type] [statements] [name = expression] [Exit Function] [statements] [name = expression] End Function

The following list describes the elements in a Function procedure declaration:

• Public: Indicates that the function is accessible to all other procedures in all other modules in the workbook (optional).

• Private: Indicates that the function is accessible only to other procedures in the same module (optional). If you use the Private keyword, your functions won't appear in the Insert Function dialog box and will not be shown in the Formula AutoComplete drop-down.

• Static: Indicates that the values of variables declared in the function are preserved between calls (optional).

• Function: Indicates the beginning of a Function procedure (required).

• Name: Can be any valid variable name. When the function finishes, the result of the function is the value assigned to the function's name (required).

• Arglist: Is a list of one or more variables that represent arguments passed to the function. The arguments are enclosed in parentheses. Use a comma to separate arguments. (Arguments are optional.)

• Type: Is the data type returned by the function (optional).

• Statements: Are valid VBA statements (optional).

• Exit Function: Is a statement that causes an immediate exit from the function (optional).

• End Function: Is a keyword that indicates the end of the function (required).

Choosing a Name for Your Function

Each function must have a unique name, and function names must adhere to a few rules:

• You can use alphabetic characters, numbers, and some punctuation characters. However, the first character must be alphabetic.

• You can use any combination of uppercase and lowercase letters.

• You can't use a name that looks like a worksheet cell's address (such as J21). Actually, you can use such a name for a function although doing so can cause unexpected results.

• You can use mixed case. VBA does not distinguish between cases. To make a function name more readable, you can use InterestRate rather than interestrate.

• You can't use spaces or periods. To make function names more readable, you can use the underscore character (Interest_Rate).

• You can't embed the following characters in a function's name: #, $, %, &, or !. These are type declaration characters that have a special meaning in VBA.

• You can use a function name with as many as 255 characters. However, shorter names are usually more readable and easier to work with.

Using Functions in Formulas

Using a custom VBA function in a worksheet formula is like using a built-in worksheet function except that you must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook as the formula, you don't have to do anything special. If it's in a different workbook, you may have to tell Excel where to find it. You can do so in three ways:

• Precede the function's name with a file reference. For example, if you want to use a function called CountNames that's defined in a workbook named Myfuncs.xlsm, you can use a formula like the following:

  =Myfuncs.xlsm!CountNames(A1:A1000)

  If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically.

• Set up a reference to the workbook. You do this with the VB Editor's Tools References command (see Figure 23-2). If the function is defined in a referenced workbook, you don't need to use the worksheet name. Even when the dependent workbook is assigned as a reference, the Insert Function dialog box continues to insert the workbook reference (even though it's not necessary).

Figure 23-2: Use the References dialog box to create a reference to a project that contains a custom VBA function.

• Create an add-in. When you create an add-in from a workbook that has Function procedures, you don't need to use the file reference when you use one of the functions in a formula; however, the add-in must be installed. I discuss add-ins later in this chapter (see "Creating Add-Ins").

Using Function Arguments

Custom functions, like Excel's built-in functions, vary in their use of arguments. Keep the following points in mind regarding VBA Function procedure arguments:

• A function can have no argument.

• A function can have a fixed number of required arguments (from 1 to 60).

• A function can have a combination of required and optional arguments.

• A function can have a special optional argument called a paramarray, which allows a function to have an indefinite number of arguments.

All cells and ranges that are used by a function should be passed as arguments. In other words, a Function procedure should never contain direct references to cells or ranges.