XL5: "Error in Formula" Using FormulaArray Property

 Last reviewed: December 19, 1996
Article ID: Q121323
The information in this article applies to:
  • Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you use the FormulaArray property in a Visual Basic procedure to enter a formula as an array, you receive the following error message: 

   Run-time error '1005':

   Error in formula

CAUSE

You receive this error message when you use the A1 reference style in the value of the FormulaArray property. For example, you receive this error message when you run a procedure that contains the following statement: 

   Range("A2").FormulaArray = "=Sum(B1:B2)"
Note that you can use either the A1 reference style, or the R1C1 reference style in the Formula property, which is similar to the FormulaArray property.

WORKAROUND

To avoid receiving this error message when you use the FormulaArray property, use the R1C1 reference style for references, as in the following example: 

   Range("A2").FormulaArray = "=Sum(R1C2:R2C2)"
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.

MORE INFORMATION

You can use the FormulaArray property to return or set the formula of a range, entered as an array. The property returns or enters a single formula or a Visual Basic array.

You can use the Formula property to return or enter a formula for a range of cells, or for an object.

REFERENCES

For more information about the FormulaArray Property, choose the Search button in the Visual Basic Reference and type: 

   FormulaArray Property

KBCategory: kbprg kberrmsg
KBSubcategory:

Additional reference words: 1.00 5.00 err msg



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.