Make a custom function return nothing - not 0, not a blank string, but nothing

Question

I have a custom function to be called from inside a spreadsheets' cell, that I want to return nothing in some cases. In other words, I want the cell with my function to be treated exactly like an empty cell (unless the function returns a value).

The closest I can get to doing that is returning an empty string "". Unfortunately a cell with a zero length string is not treated as empty by COUNTA or COUNTBLANK, and breaks mathematical formulas (eg 1 + "" = #VALUE).

Most of my attempts to return nothing cause a 0 to be returned, but this would be interpreted quite differently by the user.

What should I do?

Tried so far:

Returns 0:
    result = null
    result = VbEmpty
    result = Range("SomeCellKnownToBeEmpty")

Returns error:
    result = Nothing

Answer: I'm now reasonably sure that this is not possible, and the best that can be done is to work around it.

Work around options:

• Return a string "-blank-" and have a VBA macro delete the contents of any cell with "-blank-". A strange approach, but fits my needs. I'm doing this as one of the steps in preparing my workbook for publishing.
• Return empty string and explicitly get other formulas in the sheet to treat "" as blank.
• Return and display 0: Return 0 and use custom formatting to hide 0.

Answer 1

The trouble you have is that a UDF will always provide a value, so as such if you have a cell with a formula in it, it cannot be blank or empty which is what you want.

I think you might need to try and handle the zero or empty strings in your other formulas or convert the bigger process to entirely VBA.

For more reading see: http://excel.tips.net/T002814_Returning_a_Blank_Value.html

Edit: Possible duplicate: Return empty cell from formula in Excel

Answer 2

The nearest I can get is to set the return value to be a Variant and return null:

Function Blah() As Variant
    Blah = Null

End Function

Sub Test()
    Range("A1").Value = Blah()
End Sub

I would advise against this though, personally; it is very unusual and may cause problems further down the line.

Answer 3

Not for COUNT… functions, but to have Excel "lift up the pen" for charts/graphs/plots have the UDF return CVErr(xlErrNull).

This is rather bizarre since a cell formula that returns NA() will cause Excel to "lift up the pen"; but a UDF that returns CVErr(xlErrNA), does NOT do the same thing. Fortunately, CVErr(xlErrNull) works.