FORMULA Function Gives Macro Error on Hidden Worksheet

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

Solution

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