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

[Corey Kosak]

It's a hack and not for everyone, but in a pinch you could return a two-cell array in the horizontal or vertical direction where the main cell has the #NA and the second cell has the explanation. As in:

  [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 new object[] { ExcelError.ExcelErrorValue, "(Invalid zip code format)" };
  }

[Corey Kosak]

Hi Matt,

It turns out that we needed similar functionality at my day job, so I was able to put something together that I think does what you want.

As Kedar mentioned above, writing a note in a cell is easy enough, but the tricky part is to clean up the sheet if the formula changes or is erased with the delete key.

The trick I was able to use (some would say abuse) is to leverage the ExcelDNA support for observables. That is, when the UDF succeeds, it returns a simple scalar. But when it fails, it writes a note to the cell and then returns an observable whose job is to yield the #N/A value and then clean up the cell when the value changes or the formula is deleted.

ExcelDNA is so good at housekeeping with observables that this works quite well. This code seems to be robust against changing the formula, erasing the formula with the delete key, and even renaming the sheet.

Also it's possible it could be written more tersely with the Reactive extension library. I'm not that good at Reactive so I don't know. Anyway I hope this code is of some use. 

Note: needs the ExcelDna.Interop package

using ExcelDna.Integration;
using System.Diagnostics;
using Application = Microsoft.Office.Interop.Excel.Application;

namespace Kosak.Example;

public static class MyFunctions {
  /// <summary>
  /// This UDF is tricky. Depending on the input, it either yields a simple scalar value,
  /// or it sets the cell note and then sets up an observable to erase the cell note when
  /// the formula is deleted.
  /// </summary>

  [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!

    var cellRef = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
    return ExcelDnaUtils.ShowError(cellRef, "invalid zip code format");
  }
}

public static class ExcelDnaUtils {
  /// <summary>
  /// Set the note in the cell to the value of 'errorText', then set up an
  /// observable whose job is to clean up the cell when the formula goes away
  /// (e.g. is deleted)
  /// </summary>
  public static object ShowError(ExcelReference cellRef, string errorText) {
    // Set the comment of the cell to the error text
    ReplaceNote(cellRef, errorText);

    object[] parms = { (long)cellRef.SheetId, cellRef.RowFirst, cellRef.ColumnFirst };
    Func<IObservable<object>> eos = () => new ErrorTextObservableWithNote(cellRef);
    return ExcelAsyncUtil.Observe("show_error", parms, eos);
  }

  public static void ReplaceNote(ExcelReference cellRef, string? newComment) {
    ExcelAsyncUtil.QueueAsMacro(() => {
      try {
        var app = (Application)ExcelDnaUtil.Application;
        var refText = (string)XlCall.Excel(XlCall.xlfReftext, cellRef, true);
        var range = app.Range[refText];

        range.Comment?.Delete();
        if (newComment != null) {
          range.AddComment(newComment);
          range.Comment!.Shape.TextFrame.AutoSize = true;
        }
      } catch (Exception ex) {
        Debug.WriteLine($"Exception: {ex}");
      }
    });
  }
}

/// <summary>
/// An observable whose job is
/// 1. To yield a single #N/A
/// 2. To remove the cell comment at the last unsubscribe
/// </summary>
public class ErrorTextObservableWithNote : IObservable<object> {
  private readonly ExcelReference _cellRef;
  private readonly HashSet<IObserver<object>> _observers = new();

  public ErrorTextObservableWithNote(ExcelReference cellRef) {
    _cellRef = cellRef;
  }

  public IDisposable Subscribe(IObserver<object> observer) {
    _observers.Add(observer);
    observer.OnNext(ExcelError.ExcelErrorNA);
    return new ActionAsDisposable(() => Remove(observer));
  }

  private void Remove(IObserver<object> observer) {
    if (_observers.Remove(observer) && _observers.Count == 0) {
      ExcelDnaUtils.ReplaceNote(_cellRef, null);
    }
  }
}

/// <summary>
/// Transform an Action into an IDisposable
/// </summary>
public class ActionAsDisposable(Action action) : IDisposable {
  public void Dispose() => action();
}

[Matt Olenich]

Thanks

In the end I did something similar BUT I either return the value OR raise an error so the cell has #Value.

But before doing either I either write a note to the cell or remove it in case its there.

I also had to start all of 'my' Notes with a prefix like Acme: <My Note> so in case the user had already added a not there mine would be added to the existing note and I would know what section of the text to start removing Note text. Since the same cell could be called multiple times with an error (the same or different) I always removed the error note first and then either added an error or left alone.

Matt

---

From: exce...@googlegroups.com <exce...@googlegroups.com> on behalf of Corey Kosak <ko...@kosak.com>
Sent: Wednesday, December 10, 2025 10:23 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: Return #VALUE from UDF - but with some explanation

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/df3e5d2e-b2e0-467e-9f2b-158aebd6818cn%40googlegroups.com.