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

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.