Returning Empty from user defined function returns

问题:

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:

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 ?

回答1:

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

回答2:

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