Description of limitations of custom functions in Excel

MORE INFORMATION

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

• Insert, delete, or format cells on the spreadsheet.
• Change another cell's value.
• Move, rename, delete, or add sheets to a workbook.
• Change any of the environment options, such as calculation mode or screen views.
• Add names to a workbook.
• Set properties or execute most methods.

The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.

During calculation, Excel examines the precedents of the cell that contains a user-defined function. If all precedents have not been calculated, Excel passes a Null or Empty cell to the function. Excel then makes sure that enough calculation passes occur for all precedents to be calculated. During the final calculation pass, the user-defined function is passed the current values of the cells. This can cause the user-defined function to be called more frequently than expected. Therefore, the user-defined function may return unexpected values.

For correct calculation, all ranges that are used in the calculation should be passed to the function as arguments. If you do not pass the calculation ranges as arguments, instead of referring to the ranges within the VBA code of the function, Excel cannot account for them within the calculation engine. Therefore, Excel may not adequately calculate the workbook to make sure that all precedents are calculated before calculating the user-defined function.