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

• 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))}

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