You have already learned about and created numerous procedures. These can be executed either as macros from within Excel or directly started from the VBA editor (VBE).
In addition, you can write procedures and functions that are called by other procedures or functions.
Advantages of Functions:
- Reuse: Common or similar operations need to be programmed only once and can be executed repeatedly.
- Modularity: Large programs can be modularized into smaller, clearer parts that are easier to maintain.
- Extended functionality: Custom functions can provide capabilities beyond Excel’s built-in functions.
They can be used directly in worksheet cells just like built-in functions by entering =FUNCTIONNAME(). These appear in the User Defined category in Excel.
Main Differences Between Functions and Procedures:
- Functions execute a set of instructions and return a value, e.g., the result of a calculation.
- Procedures perform actions but do not return a value.
Parameters:
- Both procedures and functions can accept parameters, making them more flexible.
- The handling of parameters (passed by reference or by value, optional parameters, or variable numbers of parameters) is the same for both.
- For details on parameter passing, see Section 5.3.2, Passing Parameters.
Additional Note:
In the context of Excel objects, the term method is often used. Methods are also functions but are specifically related to a particular object.