Free Support Forum - aspose.com

CalculateFormula doesn't throw exception

Aspose.Cells Product Family
#1

I'm using Aspose.Excel 3.6.1.0.

I've added a VBA function to an excel file: A1 contains some text and B1 contains the formula '=MyFunc(A1)'.

When I run following code, I get the results as mentioned in the comments:

// Open file
Excel excelObject = new Excel();
excelObject.Open(@"MyFunc.xls");
// Remark1: The Formula property returns the (faulty) string: "=MyFunc,A1" (should be: "=MyFunc(A1)")
string formula = excelObject.Worksheets[0].Cells["B1"].Formula;
// valFormula is false
bool valFormula = excelObject.ValidateFormula(excelObject.Worksheets[0].Cells["B1"].Formula);
// Remark2: CalculateFormula throws no error
excelObject.CalculateFormula(false);

// Change the input parameter A1
excelObject.Worksheets[0].Cells["A1"].PutValue("Inside C#");
// Remark3: CalculateFormula throws no error
excelObject.CalculateFormula(false);
// val contains the old value (CalculateFormula can not calculate new value)
string val = excelObject.Worksheets[0].Cells["B1"].StringValue;

// Add the same formula to different cell
excelObject.Worksheets[0].Cells["B2"].Formula = "=MyFunc(A1)";
// Remark4: CalculateFormula throws no error
excelObject.CalculateFormula(false);
// val contains 0
val = excelObject.Worksheets[0].Cells["B2"].StringValue;

// Copy the formula from B1
excelObject.Worksheets[0].Cells["B2"].Formula = excelObject.Worksheets[0].Cells["B1"].Formula;
// Remark5: CalculateFormula thows error (Error in calculating cell B2) because string is malformatted (See Remark 1)
excelObject.CalculateFormula(false);

So I have two problems:

1. I would expect that CalculateFormula throws an error as soon as it encounters a function it does not know (see Remarks 2,3,4)

2. The Formula property of the cell object should return the literal formula as written in the excel file, even if Aspose can not calculate it (see Remark 1,5)

Kind regards,

Thijs

#2

Hi Thijs,

1. When meeting unkown functions, Aspose.Excel just ignore it. That's requested by other users. And I think it's reasonable. Aspose.Excel will calculate what it can do and ignore what it cannot.

2. I will fix this issue.

#3

I understand that unknown functions shouldn't block the calculation of other cells.

But how can I be sure that the value I retrieve from the cell has been calculated correctly? E.g. the value below Remark3 contains the old value instead of the newly calculated value.

Maybe CalculateFormula could return a list of cells that Aspose was not able to calculate, so I can try to do the calculation myself by parsing the formula.

#4

Please check http://www.aspose.com/Community/Forums/38932/ShowPost.aspx . We are thinking of providing an interface to let you extend the calculation engine.