Microsoft Office Tutorials and References
In Depth Information
To create a custom function, start by inserting a VBA module. You can use an existing module, as
long as it’s a normal VBA 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, you can’t use the function in a worksheet
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. In
the case of 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:
h If you don’t declare a function’s scope, its default is Public .
h Functions declared As Private don’t appear in Excel’s Insert 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.
h 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)
Executing function procedures
Although you can execute a Sub procedure in many ways, you can execute a Function
procedure in only four ways:
h Call it from another procedure.
h Use it in a worksheet formula.
h Use it in a formula that’s used to specify conditional formatting.
h Call it from the VBE Immediate window.