Microsoft Office Tutorials and References
In Depth Information
From a procedure
You can call custom functions from a VBA 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
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:
h 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:
If you insert the function with the Insert Function dialog box, the workbook reference is
h 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
h 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.