Create a custom function return nothing - not 0, not an empty string, but nothing

Question

I have a special function that is called from within a spreadsheet cell, and I want to not return anything in some cases. In other words, I want the cell with my function to be treated exactly like an empty cell (if the function does not return a value).

The closest I can do is return the empty string "" . Unfortunately, a cell with a zero string of length is not processed as COUNTA or COUNTBLANK and breaks up mathematical formulas (for example, 1 + "" = #VALUE ).

Most of my attempts to return nothing result in a return of 0, but the user will interpret it differently.

What should I do?

Tried so far:

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

Answer: Now I am sure that this is impossible, and the best that can be done is to get around this.

Work with the parameters:

• It returns the string "-blank-" and the VBA macro deletes the contents of any cell using "-blank-" . Strange approach, but suitable for my needs. I do this as one of the steps in preparing my book for publication.
• Return an empty string and explicitly get other formulas on the worksheet to treat "" as blank.
• Return and display 0: return 0 and use special formatting to hide 0.

Answer 1

The problem is that UDF will always provide a value, so if you have a cell with a formula in it, it cannot be empty or empty, which is what you need.

I think you might have to try to process null or empty lines in other formulas or convert a larger process to fully VBA.

Read more: http://excel.tips.net/T002814_Returning_a_Blank_Value.html

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

Answer 2

The closest I can get is to set the return value as Variant and return null:

 Function Blah() As Variant Blah = Null End Function Sub Test() Range("A1").Value = Blah() End Sub 

I would advise this, though personally; this is very unusual and can cause problems down the line.

Answer 3

Not for COUNT functions ... but for Excel to "lift the pen" for charts / graphs / graphs, UDF CVErr (xlErrNull) is returned.

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

Answer 4

I had the same problem. So what I did was create a dummy variable and set it equal to a function.

I used some variables and then displayed them in a message box, and I did not want to return any values, so I did this:

x = myfunction (a, b)

It worked great.

Answer 5

You should return an error, for example:

 Function retError() ' This function returns an error. retError = CVErr(vbValue) End Function 

In the cell, it will display as #VALUE! , which has the advantage of spreading through formulas in a spreadsheet (make sure the user knows that there is an error), while it is not counted by some functions, such as COUNT . Note that COUNTA still counts these cells.