Returning calculated formula value in UDF

I am trying to write a UDF. I can successfully pass constants to my delphi code but I can't figure out how to pass the value of a cell containing a formula. Have tried <#evaluate()>, <#ref()> and <#=()> without success. Is it even possible to pass the formula result via the UDF?

Hi,
Sadly and in general, no, you can't pass a cell value to a UDF (or anywhere else, like a report expression). There might be specific cases where it is possible, but in general it is not. There are 2 reasons for that:

  1. For performance reasons, FlexCel is not constantly recalculating the spreadsheet while it is filling the values. Imagine how slow it would be if every time we fill a new cell we recalculate the full thing.
    What we do instead is to fill all the values, and after the report is finished (before saving the file), we recalculate it.
    But this means that all formulas in the file have no valid values until they are calculated at the end. When FlexCel is evaluating the UDF, the formula hasn't been calculated yet.

  2. Even if there wasn't a formula in the cell, it is not safe to reference its value from an expression, udf, etc. This is because the filling order of cells isn't guaranteed.
    Imagine you have something like <#db.field> in A1, and then <#evaluate(=A1 & " verified")> in cell B1. If we fill from left to right (which is currently the case), then when we go to evaluate cell B1, A1 has already been evaluated and it will work. But the order in which we fill the cells is not guaranteed. In fact, for many years FlexCel used to fill the values from right to left and from bottom to top (again, performance reasons). If we were filling the file from right to left, then when we evaluate B1, A1 has not been filled yet, and it will be empty.
    Realistically, it is not likely we will switch back again from left-to-right to right-to-left (this could break stuff and we don't want that to happen), but we might in the future switch to a "parallel fill" where all cells are filled at the same time in different threads, and so A1 might be filled after B1 or not, depending on the case, and the results would be undefined depending on the actual fill order.
    (note: if we ever do such a change to parallel filling, we will make sure we don't break existing reports that assume a left-to-right order even if it shouldn't be assumed. We don't like to break stuff, even if technically it was never right to assume left-to-right ordering)

So the golden rule here is don't reference cells that are being filled in, and don't reference formulas in the sheet. The cells that are being filled in might not have been filled yet, and the formulas for sure won't be calculated when FlexCel evaluates the expression.