Microsoft KB Archive/84076

{|
 * width="100%"|

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

 * }

-

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.