8

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.
Andy G
  • 18,518
  • 5
  • 42
  • 63
WoodenKitty
  • 6,101
  • 8
  • 47
  • 70
  • For functions like countblank it wouldn't matter what the function returned-the mere presence of a formula means the cell isn't blank. I'm afraid excel won't allow what you want-note that if you use =a1 where a1 is empty you return 0 – JosieP Jun 05 '13 at 06:47
  • Despite what others say, you may get a true blank as a formula result: https://stackoverflow.com/a/39351425/1903793 – Przemyslaw Remin Nov 07 '17 at 12:08

5 Answers5

5

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

Community
  • 1
  • 1
CuberChase
  • 4,330
  • 5
  • 31
  • 50
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.

Andy G
  • 18,518
  • 5
  • 42
  • 63
  • I'm calling the function from a cell - perhaps the result is different to when the value is set in the way your code shows. When you're calling the function from a cell, and the function returns Null, the cell will display 0. – WoodenKitty Jun 05 '13 at 05:32
1

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.

Rocky Scott
  • 401
  • 3
  • 11
0

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

The function I had took some variables and then displayed them in a message box and I didn't want to return any values so I did this:

x = myfunction(a,b)

It worked perfectly.

user2719206
  • 1
0

You should return an error, like this:

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

In a cell, it would display as #VALUE!, which has the benefit of propagating through formulae in spreadsheet (make sure user knows there's an error), while it doesn't get counted by some functions like COUNT. Note that COUNTA still counts those cells.

Ronan Paixão
  • 6,454
  • 1
  • 24
  • 22