XL:Custom Functions Can't Change Microsoft Excel Environment

 Last reviewed: February 2, 1998
Article ID: Q170787
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel 98 Macintosh Ediiton
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c

SUMMARY

In the versions of Microsoft Excel listed at the beginning of this article, 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.
  • 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.

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.

Additional query words: vba
Keywords : kbprg xlvbainfo PgmOthr
Version : MACINTOSH:5.0, 5.0a,98;WINDOWS:5.0,5.0a,5.0c,7.0,97
Platform : MACINTOSH WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.