Is it possible to ignore formulas with user defined functions?

Aspose.Cells Product Family
1

I’m processing workbooks that could have user defined functions (UDF) in the formulas and I will have no knowledge of these UDF’s. In order to ensure that cell values are up to date, I’m calling Workbook.CalculateFormula and handling UDF’s by returning the current cell value similar to the following:


private class CustomFunction : ICustomFunction

{

public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)

{

var cell = contextObjects[2] as Cell;

	<span style="color:blue;">return</span> cell?.Value;
}



}

This will likely work fine when the formula is simple, e.g. “=SomeUdf()”, but would work incorrectly if the formula does anything more complicated such as “=SomeUdf() + 2”. Ideally, I would like to just retain the current cell value if the cell has a formula with a UDF.


Is there any way that I could prevent the formula from being evaluated if it includes a user defined function? Is there a better way to handle this scenario?

I’m guessing not, but wanted to double check. Thanks.
2

Hi,


Thanks for your query.

Well, when you use Workbook.CalculateFormula() method, you cannot prevent the custom formula/function from being evaluated. I think if possible, you may try to use Worksheet.CalculateFormula() method instead (you may choose suitable overloaded version of the method), so you may calculate formulas in specific sheets only which don’t have custom functions defined. Also, you may try to use Cell.Calculate() method which calculates the formula for the cell only.

Hope, this helps a bit.

Thank you.