Microsoft Office Tutorials and References
In Depth Information
• Public: Indicates that the function is accessible to all other procedures in all other modules in the workbook
• Private: Indicates that the function is accessible only to other procedures in the same module (optional). If
you use the Private keyword, your functions won't appear in the Insert Function dialog box and will not be
shown in the Formula AutoComplete drop-down list.
• Static: Indicates that the values of variables declared in the function are preserved between calls (optional).
• Function: Indicates the beginning of a Function procedure (required).
• Name: Can be any valid variable name. When the function finishes, the result of the function is the value as-
signed to the function's name (required).
• Arglist: A list of one or more variables that represent arguments passed to the function. The arguments are
enclosed in parentheses. Use a comma to separate arguments. (Arguments are optional.)
• Type: The data type returned by the function (optional).
• Statements: Valid VBA statements (optional).
• Exit Function: A statement that causes an immediate exit from the function (optional).
• End Function: A keyword that indicates the end of the function (required).
Choosing a name for your function
Each function must have a unique name, and function names must adhere to a few rules:
• You can use alphabetic characters, numbers, and some punctuation characters. However, the first char-
acter must be alphabetic.
• You can use any combination of uppercase and lowercase letters. VBA does not distinguish between
cases. To make a function name more readable, you can use InterestRate rather than interestrate.
• You can't use a name that looks like a worksheet cell's address (such as J21 or SUM100). Actually, Ex-
cel allows you to use such a name for a function, but the function returns a #REF! error.
• You can't use spaces or periods. Many of Excel's built-in functions include a period character, but that
character is not allowed in VBA function names. To make function names more readable, you can use the
underscore character (Interest_Rate).
• You can't embed the following characters in a function's name: #, $, %, &, or !. These are type declara-
tion characters that have a special meaning in VBA.
• You can use a function name with as many as 255 characters. However, shorter names are usually more
readable and easier to work with.
UPPERCASE function names?
You've probably noticed that Excel's built-in worksheet function names always use uppercase characters. Even if
you enter a function using lowercase characters, Excel converts it to uppercase.
When you create custom worksheet functions, you can use uppercase, lowercase, or mixed case. It doesn't mat-
ter all. When I create functions that are intended to be used in worksheet formulas, I like to make them uppercase
to match Excel's style.