Summary
In the versions of Microsoft Excel listed in the "Applies To" section, you can create a user-defined function that returns a custom calculation by using Visual Basic for Applications. However, user-defined functions cannot perform actions that change the Microsoft Excel environment when called by a formula in a worksheet cell.
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 not all precedents have been calculated so far during the calculation process, Excel eventually calls the user-defined function and 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, and with unexpected arguments. 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.
References
"Visual Basic User's Guide" version 5.0, Chapter 4, "What Is a Visual Basic Procedure"
"Writing a user-defined worksheet function", Microsoft Excel 7.0 Visual Basic Help File.
