Return #VALUE from UDF - but with some explanation

[Matt Olenich]

A simple UDF will return #Value and another cell will see this as isError() = True

All good.

But I want to tell the user 'WHY' there was an error and the basis ones (DIV0, REF etc) are insufficient.

I need to return 'something' so the user knows why the error occured like 'invalid zip code format' or something.

I though of returning ExcelError.ExcelErrorValue  and ALSO writing to the cells 'Note' attribute ... but maybe there's a better way.

I saw something on MSFT web site about a CustomError class but it only seemed to be supported in TypeScript.

[ExcelFunction(Category = "Test",Description = "Force an error")]
public static object  ForceError(String pText)
{
    if (pText == "No") return "No Error";

    // need to provide some way to tell the user WHY other than #Value!
    return ExcelError.ExcelErrorValue;
}

[Kedar Kulkarni]

We used to do it following way..

= myFUnction("msft.us","currentprice") would return #value when market price is not available.. User could just pass =myfunction("msft.us","currentprice","verboseerror=true")

so passing some optional parameter could be a good idea. The iserror/iferror construct works with only excel errors like #N/A, #Value, #Null etc. I am unsure if we have any new errors that we can introduce that would be treated by excel as errors but still provide any additional info.

the options could be to add logs from xll. 

We had tried to add a comment to the cell, but clearing cell when the value is available is again a pain. Its not worth it.

thanks

Kedar