Set error value using Range.Value

Jan 28, 2014 at 2:48 AM

I have an array, let's say {"One", "Two", ExcelError.ExcelErrorNull, "Four"}.
If I return this as array formula, excel shows #NULL! in the third cell as expected.
But if I get a range object for, let's say A1:A4, and call range.Value = above array, third cell doesn't show #NULL!, but shows 0.
I think this 0 comes from the enum value ExcelError, because if I put ExcelErrorRef in there, I see 23.

Is there a way to set a cell's value to be one of the error values?

Jan 28, 2014 at 1:23 PM

When you call Range.Value you are making a COM call to Excel, while the ExcelError codes relate to the Excel C API. That explains the different behaviour.

You can convert from the ExcelError codes to COM values using: ExcelDna.Integration.ExcelErrorUtil.ToComError(excelError).
See also

Marked as answer by sumpan on 1/28/2014 at 9:10 AM