Excel: =RESULT(8+64) Causes #VALUE! with 1-by-1 Array

ID: Q84076

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
Microsoft Excel for OS/2, version 3.0

SYMPTOMS

When you are using RESULT() within a function macro in Microsoft Excel, specifying a return value of 72 (8+64=reference and array) will cause the function macro to return #VALUE! when specifying a 1-by-1 return array (one cell).

Note: this also applies to version 5.0 when you use version 4.0 macrosheets. It does not apply to Visual Basic for Applications modules.

WORKAROUNDS

In most cases, the DEREF() is not necessary. The problem will be corrected if you stop using it.

Use =RESULT(75). This will specify result types 1, 2, 8 and 64 (number, text, reference and array).

Steps to Reproduce Problem

Type the following into a macro sheet, and define cell A1 as a function macro:


   A1: =RESULT(72)
   A2: {=RETURN(DEREF(A1))}

Note: the curly brackets are not typed into cell A2, the contents of A2 are entered as an array formula. In Microsoft Excel for Windows, use CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, use COMMAND+RETURN.

In a worksheet, paste the function into a cell. Since the array only has one element, it is not necessary to enter the formula as an array formula. The result of the function will be #VALUE!. This does not occur on arrays larger than 1-by-1.

REFERENCES

"Function Reference" for Windows, version 4.0, page 361
"Function Reference" for the Macintosh, version 3.0, pages 198-199
"Function Reference" for Windows, version 3.0, pages 198-199

Additional query words: 3.0 5.0

Keywords :
Version :
Platform :
Issue type :