Chapter 40: Creating Custom Worksheet Functions
827
Note
Step 3 is very important. If you put a function procedure in a code module for ThisWorkbook or a worksheet
(for example, Sheet1), the function will not be recognized in a worksheet formula. Excel will display a #NAME?
error. n
Function names that are used in worksheet formulas must adhere to the same rules as variable
names.Almost everyone who starts creating custom worksheet functions using VBA makes a fatal mistake:
They try to get the function to do more than is possible.A worksheet function returns a value, and the function must be completely “passive.” In other words,
the function can’t change anything on the worksheet. For example, you can’t develop a worksheet
function that changes the formatting of a cell. (Every VBA programmer has tried, and not one of them
has been successful!) If your function attempts to perform an action that isn’t allowed, the function
simply returns an error.VBA functions that aren’t used in worksheet formulas can do anything that a regular Sub procedure can
do — including changing cell formatting.What a Function Can’t Do
Executing Function Procedures
You can execute a Sub procedure in many ways, but you can execute a Function procedure in
just two ways:l (^) Call it from another VBA procedure.
l 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 arguments.
The function’s result is assigned to the Tax variable.