Custom error identifier

[dberardo-com]

when using custom formulas: is it possible to return custom errors like:

FORMULAS["CUSTOM"] =lambda x:
  if x <= 0 : return "#VALUE!"
  else: return 1/x

formula = "=SUM(1, CUSTOM(-1))"

at the moment running the formula above will result in a typeError since SUM is trying to add 1 with a string.

is there a proper way to achieve this?

---

Also, as a side question:

in case of a graph like this:

=SUM(1, CUSTOM_A(-1)) + SUM(1, CUSTOM_A(-2)) + SUM(1, SUM( 2, CUSTOM_A(-3) ) )

will all "leave nodes" (CUSTOM_A(*))` be executed or will only the first one be executed and then everything stops because of the typeError above? Or is maybe the last one that getts executed first, then all the rest, since it is a more nested node ?

[vinci1it2000]

1. The proper way is to use the XlError defined by formulas (i.e., NULL, DIV, VALUE, REF, NUM, NAME, and NA).

>>> import formulas
... formulas.get_functions()["CUSTOM"] = lambda x: formulas.VALUE if x <= 0  else 1 / x
... formulas.Parser().ast(formula)[1].compile()()
array([[#VALUE!]], dtype=object)

2. According to the schedule workflow all functions will be executed.

[dberardo-com]

thanks for the reply!

ok so from point 1 above i understand that instead of using "#VALUE!" i should use the actual object xlError, so something like:

FORMULAS["CUSTOM"] =lambda x:
  if x <= 0 : return xlError.SOME_ERROR # <--- this line has changed
  else: return 1/x

formula = "=SUM(1, CUSTOM(-1))" # <-- this will output #VALUE

is this correct ?

and from point 2 above "all functions will be executed." do you mean only the "leaf nodes" or all formulas that have no error ? so i guess the question translates to:

=SUM(1, CUSTOM_A(-1)) + SUM(1, SUM( 2, 5 ) ) + SUM(1, CUSTOM_A(-2))

will the intermediate block SUM(1, SUM( 2, 5 ) ) be executed completely or only the SUM(2,5) part (leaf node) ?