Part VI: Automating Excel
Executing Function Procedures
You can execute a Sub procedure in many ways, but you can execute a function procedure
in just two ways:■ (^) Call it from another VBA procedure.
■ (^) Use it in a worksheet formula.
Calling custom functions from a procedure
You can call custom functions from a VBA procedure just as you call built-in VBA functions.
For example, after you define a function called CalcTax, you can enter a statement such as
the following:
Tax = CalcTax(Amount, Rate)
This statement executes the CalcTax custom function with Amount and Rate as its argu-
ments. The function’s result is assigned to the Tax variable.
Using custom functions in a worksheet formula
You use a custom function in a worksheet formula just as you use built-in functions.
However, you must ensure that Excel can locate the function. If the function procedure is
in the same workbook, 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:
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 workbook
name. Here’s an example:
='My Functions.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 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’re presented with a list of references that includes all open workbooks. Place a
check mark in the item that refers to the workbook containing the custom function. (Click
the Browse button if the workbook isn’t open.)