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