"Formula Not Valid" Converting or Assigning Valid Formula (124886)
The information in this article applies to:
- Microsoft Excel for Windows 95 7.0a
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for Windows NT 5.0
- Microsoft Excel for the Macintosh 5.0a
This article was previously published under Q124886 SYMPTOMS
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
CAUSE
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.
WORKAROUND
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.
Method 1
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.
Method 2
Use shorter formulas when possible.
STATUS
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.
REFERENCES
For more information about the Formula Property, choose the Search
button in the Visual Basic Reference and type:
For more information about the ConvertFormula Method, choose the
Search button in the Visual Basic Reference and type:
| Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
|---|
| Keywords: | kbbug kbfix kbProgramming KB124886 |
|---|
|