Passing empty (but valid) data from CalculateCustomFunction

Aspose.Cells Product Family
1

As I understand it, CalculateCustomFunction should return null if there's no valid data (correct me if I'm wrong).

But what should I return if the return value is valid, but empty? Right now I return an empty string, but that doesn't feel right since there's a difference between null and an empty string.

Thanks!
/Fredrik

2

Hi,
Well, the CalculateCustomFunction should return null when the data is invalid.
We are not sure about your scenario, could you elaborate and explain it in detail, so that we understand it completely. We will check it soon.

Thank you.

3

Our custom function is a "VLookup on steroids" - given the parameters, it looks up the value in another cell in the workbook, and returns it.

This works fine for all values except when the value of the cell is null. In this case, we can't return null since this indicates an error, although null is indeed a valid value for an Excel cell. We can't return an empty string, because we need to differentiate between null values and empty strings.

Our workaround now is really awkward: We return a strange character (¤), which normally should never appear in our worksheets. When CalculateFormula has finished, we iterate over all cells in the workbook, and do a PutValue(null) for all cells with a '¤' in them.

Perhaps you could introduce a special object to return, which indicates null and not an error?Something like CellNull.Value. (Like DBNull.Value in ADO.NET)

4

Hi,

In the current Aspose.Cells version, custom functions return null, we will process it as ErrorType.NA. If formulas return null, we will process it as zero as MS Excel does. So, we could not keep a null as the formula value. You have to process null by yourself, I am afraid.

Thank you.

5

Yes, I understand how it works today. But don't you agree that there's something missing if it's impossible to return null from a custom function? Null is indeed a valid value in cells, and why limit the custom functions from returning that?

I have worked around it already, but please consider fixing this for a future release. Perhaps using the "CellNull.Value" I mentioned.

/Fredrik

6

Hi Fredrik,

Thank you for considering Aspose.

We will not process null as Error.NA if custom function returns null. But if the formula’s value is null, we will still set zero as the cell value.

Thank You & Best Regards,

7

Hi Fredrik,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We will not process null as Error.NA if custom function returns null. But if the formula’s value is null, we will still set zero as the cell value.

Thank You & Best Regards,

8

But how do I now return an invalid value (to be treated as Error.NA)? And isn't there a chance that this change would break other peoples' implementations, where it is assumed that the return value null indicates an error?

Wouldn't it be better to introduce a special "Null"-object, like CellNull.Value?

/Fredrik

9

Hi Fredrik,

Thank you for considering Aspose.

Well, I am afraid we can not support “CellNull.Value”. If you want to return the error values you may use the following constants (as we see in MS Excel):

  • internal const string ErrorDiv = “#DIV/0!”;
  • internal const string ErrorValue = “#VALUE!”;
  • internal const string ErrorNA = “#N/A”;
  • internal const string ErrorName = “#NAME?”;
  • internal const string ErrorNumber = “#NUM!”;
  • internal const string ErrorRecursive = “#Recursive Reference!”;
  • internal const string ErrorNull = “#NULL!”;
  • internal const string ErrorRef = “#REF!”;

Thank You & Best Regards,