ID: Q124886
The information in this article applies to:
In Microsoft Excel, when you use the Formula property or the ConvertFormula method in a Visual Basic for Applications procedure, you may receive the one of the following error messages when you run the procedure, even if the formula that you assign or convert is valid:
Microsoft Excel, version 7.0
----------------------------
Run-time error '1005':
Error in formula
Microsoft Excel, version 5.0
----------------------------
Run-time error '1005':
Formula is not valid
This problem occurs when you assign a formula using the Formula property, because Microsoft Excel converts the A1-style notation formula to R1C1- style notation. The error message appears if the resulting formula in the R1C1-style notation exceeds 255 characters, the character limit for a cell in Microsoft Excel. Note that a formula in R1C1 notation generally contains more characters than the same formula in A1 notation.
When you use the ConvertFormula method to convert a formula from A1 to R1C1 reference styles, from relative to absolute references, or both, you may also receive this error message because the converted formula exceeds the 255 character limit.
When you assign a formula using the FormulaR1C1 property, the 255 character limit applies as expected. However, when you assign a formula using the Formula property, the 255 character limit applies to the size of the resulting R1C1 formula, not just the size of the A1-style notation formula itself.
When Microsoft Excel converts a formula from A1-style notation to R1C1- style notation (note that this occurs when you use the Formula property or the ConvertFormula method), the length of the R1C1 reference is assumed to be equal to the number of characters used for the column and row numbers plus six (for the letters "r" and "c" and two pairs of square brackets). This is generally correct for relative references. For example, a formula in cell A1 that references cell B2 is written as R[1]C[1]. However, this assumption adds unnecessary characters in some cases. For example, a formula in cell A1 that references cell A2 uses as many characters as the reference R[1]C[0], instead of using only the necessary characters in the reference R[1]C.
When you use an absolute reference, the length of the formula may also be larger than it needs to be. For example, $A$2 is converted to R2C1, requiring the same number of characters. However, the Formula property and the ConvertFormula method will assume that 8 characters are required.
To avoid this error when you use the Formula property or the ConvertFormula method with a long A1 style formulas, especially using absolute references, use any of the following methods.
Use R1C1-style references in your formula instead of A1-style references so that you can count on the expected limit of 255 characters.
NOTE: If possible, you should also use absolute references rather than relative references.
Use shorter formulas when possible.
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.
For more information about the Formula Property, choose the Search button in the Visual Basic Reference and type:
Formula Property
ConvertFormula Method
Keywords : kbprg PgmOthr
Version : WINDOWS:5.0,5.0c,7.0,7.0a; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug
Solution Type : kbfixLast Reviewed: February 4, 1998