Run-time error using FormulaArray property

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q213181
When you try to create an array formula by using a Visual Basic forApplications macro, you may receive the following error message:
Run-time error '1004':
Unable to set the FormulaArray property of the Range class.
This problem occurs when you try to pass a formula that contains more than255 characters, and you are using the FormulaArray property in Visual Basic for Applications.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
An array formula can perform multiple calculations and then returns eithera single result or multiple results. You create array formulas the same waythat you create basic, single-value formulas. However, with an arrayformula, after you create the formula, instead of entering the formula bypressing the ENTER key, you enter the formula by pressing theCTRL+SHIFT+ENTER keys.

An array formula can be identified 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.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
213841 Passed strings longer than 255 characters are truncated
212172 "Unable to record" message when recording a macro
For more information about the FormulaArray property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type FormulaArray in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Article ID: 213181 - Last Review: 09/20/2010 17:39:00 - Revision: 6.0

Microsoft Excel 2000 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010

  • kbbug kberrmsg kbpending KB213181