Excel Integration - Return Value of function Infor.Read.Value

lindenbauer-michael

Dear all, 
we have a formula Infor.Read.Value with a non-existing element. So the return value of the formula is #VALUE!
=INFOR.READ.VALUE("CPM_CPM";$F$3;"Value";J4;J5;J6;J7)

Unfortunately #VALUE! is not recognized as an error. In the past we used following formula, which doesn't work now. 
=IFERROR(INFOR.READ.VALUE("CPM_CPM";$F$3;"Value";J4;J5;J6;J7);0) >> Result = #VALUE!

If we change the formula a little bit and multiplicate the result with 1, then it works.
=IFERROR(INFOR.READ.VALUE("CPM_CPM";$F$3;"Value";J4;J5;J6;J7)*1;0) >> Result = 0

Can we change any setting, so we do not have to adapt all formulas?

Kind regards,

Michael

Vit Holy

Hello Michael,

unfortunately this was changed by Microsoft. In the past, when our custom functions returned #N/A or #VALUE! it was treated as an appropriate error. Once MS introduced custom functions error support (which we are going to adopt - no timeline, sorry) they stopped treating these as an error and instead treat them as a string with value '#N/A'.

If you use latest Microsoft 365 Office, then you can eventually create your own function to check for such error, i.e. you will leverage Lambda function combined with Name Manager, see https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

This Lambda function can be named e.g. IFERR, then you can replace the native IFERROR with that new one.

HTH,

Vit.