Article ID: 170787 - View products that this article applies to.
This article was previously published under Q170787
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.
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:
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.
"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.
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 170787 - Last Review: February 16, 2012 - Revision: 1.0
Contact us for more help
Connect with Answer Desk for expert help.