Function Procedures | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

A custom Function procedure has much in common with a Sub procedure. (For more information on Sub procedures, see Chapter 9.)

Declaring a function

The syntax for declaring a function is as follows :

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

The Function procedure contains the following elements:

Public : (Optional) Indicates that the Function procedure is accessible to all other procedures in all other modules in all active Excel VBA projects.
Private : (Optional) Indicates that the Function procedure is accessible only to other procedures in the same module.
Static : (Optional) Indicates that the values of variables declared in the Function procedure are preserved between calls.
Function : (Required) Indicates the beginning of a procedure that returns a value or other data.
name : (Required) Represents any valid Function procedure name, which must follow the same rules as a variable name.
arglist : (Optional) Represents a list of one or more variables that represent arguments passed to the Function procedure. The arguments are enclosed in parentheses. Use a comma to separate pairs of arguments.
type : (Optional) Is the data type returned by the Function procedure.
instructions : (Optional) Are any number of valid VBA instructions.
Exit Function : (Optional) Is a statement that forces an immediate exit from the Function procedure prior to its completion.
End Function : (Required) Is a keyword that indicates the end of the Function procedure.

The main thing to remember about a custom function written in VBA is that a value is always assigned to its name a minimum of one time, generally when it has completed execution.

To create a custom function, start by inserting a VBA module. (Or you can use an existing module.) Enter the keyword Function , followed by the function name and a list of its arguments (if any) in parentheses. You can also declare the data type of the return value by using the As keyword (this is optional, but recommended). Insert the VBA code that performs the work, making sure that the appropriate value is assigned to the term corresponding to the function name at least once within the body of the Function procedure. End the function with an End Function statement.

Function names must adhere to the same rules as variable names . If you plan to use your custom function in a worksheet formula, be careful if the function name is also a cell address. For example, if you use something like J21 as a function name, it must be entered with apostrophes :

 ='J21'(A1)

The best advice is to avoid using function names that are also cell references, including named ranges. And, avoid using function names that correspond to Excel's built-in function names. If there is a function name conflict, Excel always uses its built-in function.

A function's scope

In Chapter 9, I discuss the concept of a procedure's scope (public or private). The same discussion applies to functions: A function's scope determines whether it can be called by procedures in other modules or in worksheets.

Here are a few things to keep in mind about a function's scope:

If you don't declare a function's scope, its default is Public .
Functions declared As Private do not appear in Excel's Paste Function dialog box. Therefore, when you create a function that should be used only in a VBA procedure, you should declare it Private so that users don't try to use it in a formula.
If your VBA code needs to call a function that's defined in another workbook, set up a reference to the other workbook by choosing the Visual Basic Editor (VBE) Tools References command.

Executing function procedures

Although you can execute a Sub procedure in many ways, you can execute a Function procedure in only three ways:

Call it from another procedure
Use it in a worksheet formula
Call it from the VBE Immediate window

FROM A PROCEDURE

You can call custom functions from a procedure the same way that you call built-in functions. For example, after you define a function called SumArray , you can enter a statement like the following:

 Total = SumArray(MyArray)

This statement executes the SumArray function with MyArray as its argument, returns the function's result, and assigns it to the Total variable.

You also can use the Run method of the Application object. Here's an example:

 Total = Application.Run ("SumArray", "MyArray")

The first argument for the Run method is the function name. Subsequent arguments represent the argument(s) for the function. The arguments for the Run method can be literal strings (as shown above), numbers , or variables.

IN A WORKSHEET FORMULA

Using custom functions in a worksheet formula is like using built-in functions except that you must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook, 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 name with a file reference. For example, if you want to use a function called CountNames that's defined in an open workbook named Myfuncs.xlsm , you can use the following reference:
```
 =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 so by choosing the VBE Tools References command. 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 Paste Function dialog box continues to insert the workbook reference (although it's not necessary).
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. The add-in must be installed, however. I discuss add-ins in Chapter 21.

You'll notice that unlike Sub procedures, your Function procedures do not appear in the Macro dialog box when you issue the Tools Macro Macros command. In addition, you can't choose a function when you issue the VBE Run Sub/UserForm command (or press F5) if the cursor is located in a Function procedure. (You get the Macro dialog box that lets you choose a macro to run.) As a result, you need to do a bit of extra up-front work to test your functions while you're developing them. One approach is to set up a simple procedure that calls the function. If the function is designed to be used in worksheet formulas, you'll want to enter a simple formula to test it.

FROM THE VBE IMMEDIATE WINDOW

The final way to call Function procedure is from the VBE Immediate window. This method is generally used only for testing purposes. Figure 10-3 shows an example.

Figure 10-3: Calling a Function procedure from the Immediate Window.