FORMULA Function Gives Macro Error on Hidden Worksheet

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

  1. Close all worksheets except for one macro sheet.

  2. Enter the following macro:

          A1: =FORMULA(B1,C1)
          A2: =RETURN()
    

  3. Assign a keystroke to the macro so that it can be run from the Null menu bar.

  4. From the Window menu, choose Hide.

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


KBCategory: kbprg kbmacro
KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00


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: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.