从用户定义的函数返回空值实际返回0 [英] Returning Empty from user defined function returns actually 0

问题描述

我需要用一个用户功能来填充一个单元格。如果函数的结果不同于零，单元格应该简单地填充函数的结果;如果结果为零，我想让单元格为空。

我写了这个用户定义的函数：

 函数MyTestFunction（n As Integer）As Integer 
 Dim result As Integer 
 
 result = n * 2 
 
如果result = 0然后
 MyTestFunction = Empty 
 Else 
 MyTestFunction = result 
 End If 
结束函数
  

该函数没有真正的用途，只是为了调查这个问题。 p>

Excel工作表如下所示：

单元格A1到A3只是输入数据，单元格B1到B3包含：

• B1： = MyTestFunction（A1）


• B2 ： = MyTestFunction（A2）


• B3： = MyTestFunction（A3）

现在为什么单元格B 3显示 0 而不是空？

解决方案

函数，以便始终返回一个整数，所以它是。空值不是整数。您可以按如下方式修改您的功能：

 函数MyTestFunction（n As Integer）As Variant 
 Dim result As Integer 
 
 result = n * 2 
 
如果result = 0然后
 MyTestFunction =
 Else 
 MyTestFunction = result 
结束如果
结束函数
  

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 ?

解决方案

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

这篇关于从用户定义的函数返回空值实际返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！