Microsoft KB Archive/201354

= XL2000: Run-Time Error Using FormulaArray Property =

PSS ID Number: 201354

Article Last Modified on 10/6/2003

-

The information in this article applies to:


 * Microsoft Excel 2000
 * Microsoft Visual Basic for Applications

-



This article was previously published under Q201354





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 that this is a problem in the Microsoft products that are 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.

