Part VI: Programming Excel with VBA
828
Using custom functions in a worksheet formula
You use a custom function in a worksheet formula just like you use built-in functions. However,
you must ensure that Excel can locate the function. If the function procedure is in the same work-
book, you don’t have to do anything special. If the function is defined in a different workbook, you
may have to tell Excel where to find the function. The following are the three ways in which you
can do this:l (^) 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 MyFunctions, you
can use a reference such as the following:
=MyFunctions.xlsm!CountNames(A1:A1000)
If the workbook name contains a space, you need to add single quotes around the work-
book name. For example
=’My Functions.xlsm’!CountNames(A1:A1000)
If you insert the function with the Insert Function dialog box, the workbook reference is
inserted automatically.
l Set up a reference to the workbook. If the custom function is defined in a referenced
workbook, you don’t need to precede the function name with the workbook name. You
establish a reference to another workbook by choosing Tools ➪ References (in the VB
Editor). You are presented with a list of references that includes all open workbooks. Place
a check mark in the item that refers to the workbook that contains the custom function.
(Click the Browse button if the workbook isn’t open.)
l Create an add-in. When you create an add-in from a workbook that has function proce-
dures, you don’t need to use the file reference when you use one of the functions in a for-
mula; the add-in must be installed, however. Chapter 45 discusses add-ins.
Note
Function procedures don’t appear in the Macro dialog box because you can’t execute a function directly. As a
result, you need to do extra, up-front work to test your functions while you’re developing them. One approach is
to set up a simple Sub procedure that calls the function. If the function is designed to be used in worksheet for-
mulas, you can enter a simple formula that uses the function to test it while you’re developing the function. n
Function Procedure Arguments
Keep in mind the following about function procedure arguments:l Arguments can be variables (including arrays), constants, literals, or expressions.l (^) Some functions do not have arguments.