Microsoft KB Archive/70478

{| = FORMULA Function Gives Macro Error on Hidden Worksheet =
 * width="100%"|

Last reviewed: November 30, 1994

Article ID: Q70478 The information in this article applies to:
 * Microsoft Excel for Windows, versions 3.0, 4.0
 * Microsoft Excel for the Macintosh, version 3.0

SUMMARY
The FORMULA function will return a macro error when used to move data on a hidden worksheet or an add-in (.XLA) file in Microsoft Excel when the Null bar is displayed. The Null bar is the menu that is displayed when no sheets are open.

NOTE: This does not occur in later versions of Microsoft Excel for Windows.

MORE INFORMATION
This can be seen using a Microsoft Excel add-in file that uses a custom dialog box to retrieve data. In such an example, the FORMULA statement cannot be used to move the data to another cell.

The formula statement cannot be used to move data when the Null bar (Menu ID=3) is displayed. Data can still be transferred using the SET.VALUE function.

Steps to Reproduce Problem
 Close all worksheets except for one macro sheet.  Enter the following macro: A1: =FORMULA(B1,C1) A2: =RETURN  Assign a keystroke to the macro so that it can be run from the Null menu bar. From the Window menu, choose Hide. Run the macro by using the assigned keystroke.

The result will be a macro error.

Solution
If you substitute the line =SET.VALUE(C1,B1) for cell A1, the macro will run without an error.
 * }