Executing function procedures

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

♦ Call it from another procedure

♦ Use it in a worksheet formula


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.


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.xls, you can use the following reference:


If you insert the function with the Paste 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.

0 0

Post a comment