User Defined Functions are calculating when recalculating a workbook without custom engine

Aspose.Cells Product Family
1

Hi,

PFA Workbook

UDF functions are calculating when they shouldn’t and return a #NAME error value
All UDFs on TI Summary Report values have #Name error values after recalculation.

Code to reproduce the issue:

var workbook = new Workbook(@"#Name Error.xlsx");
var cell = workbook.Worksheets["TI Summary Report"].Cells["I48"];
var valueBefore = cell.Value;

var options = new CalculationOptions
{
	CustomEngine = null, //No engine
	//Recursive = true,
	IgnoreError = true,
	PrecisionStrategy = CalculationPrecisionStrategy.Decimal
	//Recursive =  false
};

workbook.CalculateFormula(options); //After this recalculation, value in cell is now a #NAME? error

var valueAfter = cell.Value;
var equal = valueBefore == valueAfter;  // Values should be equal until calculation with a custom engine

Thanks!
Dean

2

@Moonglum,

Thanks for the template XLSX file.

I opened your Excel XLSX file into MS Excel manually and found those formulas in “TI Summary Report” are calculated as “#NAME?”. But when I unziped/extracted or opened your XLSX into some Zip tool (e.g., WinRAR), the value for I48 cell in the sheet is stored as “#OK”, see the attached screenshot for reference. Due to this cached/stored value in the file (itself), you will get “#OK” for valueBefore variable. But since it is calculated as “#NAME?”, so you will get different value for valueAfter variable.
sc_shot1.png (97.9 KB)

3

@Moonglum,
For user defined functions, if there is no custom engine then they will be calculated as “#NAME?”. It is just same with behavior of ms excel. To make the value of cell which uses those functions be calculated correctly, you have to provide custom engine to give correct value for those functions.