About Function Procedures | 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).

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

Tip

By default, all projects are named VBAProject-and that's the name that appears in the Available References list in the References dialog box. To make sure that you select the correct project in the References dialog box, keep your eye on the bottom of the dialog box, which shows the workbook name for the selected item. Better yet, change the name of the project to be more descriptive. To change the name, select the project, press F4 to display the Properties window, and then change the Name property to something other than VBAProject. It's best to use a unique name because Excel will not let you create two references with the same name.

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").

Note

None of these three methods will cause the function name to appear in the Formula AutoComplete drop-down. Formula AutoComplete works only when the formula is entered into the workbook that contains the custom function. This is a known bug in the initial release of Excel 2007, and the problem may be corrected in an update.

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.

Cross Ref

See Chapter 25 for examples of functions that use various types 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.