Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 540
Product
ExcelWriter
Title
Error: "Invalid formula, parsing failed"
Problem
This problem only applies to the ExcelApplication object.

When assigning a formula to a cell object, the following error is received:

SoftArtisans.ExcelWriter.1 error '80020009'
Invalid formula, parsing failed

Solution

If your formula references another worksheet, there are several possible causes of this error:

  1. Using characters in sheetnames which are not supported by Microsoft Excel.

    Excel itself has several restrictions with regard to sheetnames:
    • They cannot be longer than 31 characters
    • They cannot be blank
    • They cannot contain the following characters:
          : \ / ? * [ ]

  2. In addition to the built-in Excel limitations, there are certain characters which are not supported by the formula parser of ExcelWriter's ExcelApplication object. With the ExcelApplication object, you can assign any Excel-supported name you wish to a worksheet.   However if you have a formula or chart which references a worksheet, it's name must conform to some additional limitations:

    The following characters CAN be included in sheetnames which will be used in a formula:
    • Numbers 0-9, but not for the first character
    • English characters
    • Any of these characters: ~`@#$%^&_|
    • Single quotes or spaces, but not for the first character.

    Examples of sheet names which WILL NOT be accepted when referencing a different worksheet:
    • sheet names containing hyphens
    • sheet names beginning with numbers.
    • sheet names containing non-English characters


    Note:  The above issue also applies to charts created with the ExcelApplication object. If a chart references a sheet whose name contains unsupported characters, the chart will not display properly. The solution is to either rename the sheet which is referenced by the chart, or use the ExcelTemplate object.

  3. The invalid parsing error can also be caused by using single quotes around the sheet name when assigning the formula
    This is a common syntax to use in Excel, but ExcelWriter will not accept it.

    sheet2.cell(A1).Formula="='sheet1!A1'"
    The solution is to remove the single quotes:
    sheet2.cell(A1).Formula = "=sheet1!A1"


If your formula does not reference a different worksheet, the error is most likely due to one of the following causes:

  1. You are using the LE or SE edition of ExcelWriter which only supports adding SUM and AVERAGE formulas programmatically. See: http://support.softartisans.com/OfficeWriter/ExcelWriter/doc/intro/versions.asp

  2. You are using a formula which is not available in the ExcelWriter object model. For a complete list of supported formulas, see: http://support.softartisans.com/OfficeWriter/ExcelWriter/doc/reference/functions.asp

  3. You have a syntax error in your formula

Again, this problem only affects formulas assigned to cells in script using the ExcelApplication object. When using the ExcelTemplate object, any formula syntax supported by Excel can be used in your template spreadsheet.

Related Links
OfficeWriter Home Page
OfficeWriter Enterprise Edition
Latest OfficeWriter News
OfficeWriter: Programmatic Runtime Control

Created : 4/16/2004 1:56:10 PM (last modified : 4/16/2004 1:58:19 PM)
Rate this article!
 
Comments