XL2000: Run-Time Error Using FormulaArray Property

• Microsoft Excel 2000

SYMPTOMS

When you try to create an array formula by using a Microsoft Visual Basic for Applications macro, you may receive the following error message:

Run-time error '1004':

Unable to set the FormulaArray property of the Range class

CAUSE

This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications.

RESOLUTION

Do not pass formulas that contain over 255 characters to a FormulaArray in Visual Basic for Applications.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

An array formula can perform multiple calculations and then returns either a single result or multiple results. You create array formulas the same way that you create basic, single-value formulas. However, with an array formula, after you create the formula, instead of entering the formula by pressing ENTER, you enter the formula by pressing CTRL+SHIFT+ENTER.

You can identify an array formula by the braces ({}) surrounding the formula. To create an array formula in Visual Basic for Applications, you can use the FormulaArray property. However, you cannot create a formula that has more than 255 characters by using this property. Note that the Formula property in Visual Basic for Applications does not have this limit.

REFERENCES

For more information about the FormulaArray property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "FormulaArray Property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For additional information, please see the following articles in the Microsoft Knowledge Base:

Q213841 XL: Passed Strings Longer Than 255 Characters Are Truncated

Q212172 XL2000: "Unable to Record" Message When Recording a Macro

Additional query words: OFF2000 XL2000

Keywords : kberrmsg kbprg kbdta xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug