Returning Empty from user defined function returns actually 0

Question

I need to fill a cell with the result of a user function. If the result of the function is different from zero, the cell should be simply filled with the result of the function; so far so good.

But if the result is zero I want the cell to be empty.

So I wrote this user defined function:

Function MyTestFunction(n As Integer) As Integer
    Dim result As Integer

    result = n * 2

    If result = 0 Then
        MyTestFunction = Empty
    Else
        MyTestFunction = result
    End If
End Function

The function has no real purpose, it's just for investigating this issue.

The Excel sheet is like follows:

enter image description here

Cells A1 to A3 is just typed in data, cells B1 to B3 contain:

B1: =MyTestFunction(A1)
B2: =MyTestFunction(A2)
B3: =MyTestFunction(A3)

Now why does cell B3 display 0 instead of being empty ?

score 3 · Accepted Answer · answered Nov 28 '14 at 22:22

3

You have defined your function so as to always return an integer and so it does. An empty value is not an integer. You can modify your function as follows:

Function MyTestFunction(n As Integer) As Variant
    Dim result As Integer

    result = n * 2

    If result = 0 Then
        MyTestFunction = ""
    Else
        MyTestFunction = result
    End If
End Function

answered Nov 28 '14 at 22:22

Zeno

216
1
4

That works, but unfortunately the empty cell returned like this cannot be used in certain formulas. For example if I put the formula `=B2+B3` into cell B4, I get an error instead of 4 (4+0). On the other hand `=SUM(B1:B3)` works. – Jabberwocky Dec 01 '14 at 09:52
@MichaelWalz - Your example shows that the best substitute for an *Empty* depends on the intended use for the result, see [this](http://stackoverflow.com/a/27198611/2707864). – sancho.s ReinstateMonicaCellio Dec 01 '14 at 16:03
1

@Michael Walz - As sancho.s correctly points out, you cannot really return an 'empty' value. The modified version of the function that I suggested returns an empty string if the result is 0. This makes the cell appear empty when in fact it contains a string. So when you try to add an integer to a string (`=B2+B3`) you get the error. When you sum a range (`=SUM(B1:B3)`) the cell(s) containing the string is ignored so the addition is successful. A work-around for this issue would be to use the IF function when adding cell values like so: `=IF(B2 = "",0,B2)+IF(B3 = "",0,B3)`. – Zeno Dec 01 '14 at 16:51
@Zeno Thanks. I've come to the same conclusion. For addition I use now `SUM` and for substraction I write a user defined function `DIFF` that takes care of the required conversions. – Jabberwocky Dec 01 '14 at 16:56

score 3 · Answer 2 · edited May 23 '17 at 10:31

You cannot return an Empty from any formula (with either builtin or user defined functions), see this.

"" is not the same as Empty, and they behave differently in many cases. A manifestation of the difference can be obtained by executing in the immediate window the line below, on both a selected empty cell and a cell containing ""

? TypeName(Selection.Value)

The most useful substitute for Empty as the result of a formula depends on your use of the cell containing the formula, see this for a taste of it.

Still in the ToDo list of MS (or perhaps not...)

EDIT: I found the following relevant question (it is worth checking the answers)

Return empty cell from formula in Excel

Returning Empty from user defined function returns actually 0

2 Answers2

Linked