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.
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:
213841
XL: Passed Strings Longer Than 255 Characters Are Truncated
212172
XL2000: "Unable to Record" Message When Recording a Macro
Additional query words: OFF2000 XL2000
Keywords: kbbug kberrmsg kbpending kbProgramming KB201354
Technology: kbExcel2000 kbExcel2000Search kbExcelSearch kbExcelWinSearch kbVBASearch kbZNotKeyword3 kbZNotKeyword6