Declaring a function

The syntax for declaring a function is as follows:

[Public | Private] [Static] Function name ([arglist])[As type] [instructions] [name = expression] [Exit Function] [instructions] [name = expression] End Function

The Function procedure contains the following elements:

■ Public: (Optional) Indicates that the Function procedure is accessible to all other procedures in all other modules in all active Excel VBA projects.

■ Private: (Optional) Indicates that the Function procedure is accessible only to other procedures in the same module.

■ Static: (Optional) Indicates that the values of variables declared in the Function procedure are preserved between calls.

■ Function: (Required) Indicates the beginning of a procedure that returns a value or other data.

■ name: (Required) Represents any valid Function procedure name, which must follow the same rules as a variable name.

■ arglist: (Optional) Represents a list of one or more variables that represent arguments passed to the Function procedure. The arguments are enclosed in parentheses. Use a comma to separate pairs of arguments.

■ type: (Optional) Is the data type returned by the Function procedure.

■ instructions: (Optional) Are any number of valid VBA instructions.

■ Exit Function: (Optional) Is a statement that forces an immediate exit from the Function procedure prior to its completion.

■ End Function: (Required) Is a keyword that indicates the end of the Function procedure.

The main thing to remember about a custom function written in VBA is that a value is always assigned to its name a minimum of one time, generally when it has completed execution.

To create a custom function, start by inserting a VBA module. (Or you can use an existing 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, it must be entered with apostrophes:

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. If there is a function name conflict, Excel always uses its built-in function.

0 0

Post a comment