XL: Custom Functions Can't Change Microsoft Excel Environment

ID: Q170787

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition
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.

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.

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 kbdta PgmOthr KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0a,5.0c
Platform : MACINTOSH WINDOWS
Issue type : kbinfo