Description of limitations of custom functions in Excel (170787)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
  • Microsoft Excel for Windows 5.0
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for the Macintosh 5.0
This article was previously published under Q170787

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 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.

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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbfunctions kbinfo kbProgramming KB170787
©2004 Microsoft Corporation. All rights reserved.