vba 使自定义函数不返回任何内容 - 不是 0，不是空字符串，而是什么都不返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16929788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Make a custom function return nothing - not 0, not a blank string, but nothing
提问by WoodenKitty
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 COUNTAor COUNTBLANK, and breaks mathematical formulas (eg 1 + "" = #VALUE).
我能做的最接近的是返回一个空字符串""。不幸的是，具有零长度字符串的单元格不会被COUNTA或视为空COUNTBLANK，并且会破坏数学公式（例如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.
我大多数不返回任何内容的尝试都会导致返回 0，但这会被用户以完全不同的方式解释。
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.
- 返回一个字符串
"-blank-"并让 VBA 宏删除带有"-blank-". 一种奇怪的方法，但符合我的需求。我这样做是准备我的工作簿以供发布的步骤之一。 - 返回空字符串并明确获取工作表中的其他公式以将其
""视为空白。 - 返回并显示 0：返回 0 并使用自定义格式隐藏 0。
采纳答案by CuberChase
The trouble you have is that a UDF will alwaysprovide 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.
您遇到的问题是 UDF 将始终提供一个值，因此，如果您有一个带有公式的单元格，则它不能是您想要的空白或空。
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.
我认为您可能需要尝试处理其他公式中的零或空字符串，或者将更大的过程转换为完全 VBA。
For more reading see: http://excel.tips.net/T002814_Returning_a_Blank_Value.html
更多阅读请参见：http: //excel.tips.net/T002814_Returning_a_Blank_Value.html
Edit: Possible duplicate: Return empty cell from formula in Excel
编辑：可能重复：从 Excel 中的公式返回空单元格
回答by Andy G
The nearest I can get is to set the return value to be a Variant and return null:
我能得到的最接近的是将返回值设置为 Variant 并返回 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.
不过，我个人建议不要这样做；这是非常不寻常的，可能会导致进一步的问题。
回答by Rocky Scott
Not for COUNT… functions, but to have Excel "lift up the pen" for charts/graphs/plots have the UDF return CVErr(xlErrNull).
不是用于 COUNT... 函数，而是让 Excel 为图表/图形/绘图“举起笔”让 UDF 返回 CVErr(xlErrNull)。
This is rather bizarre since a cell formulathat 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.
这很奇怪，因为返回NA()的单元格公式会导致 Excel“举起笔”；但是返回 CVErr(xlErrNA) 的 UDF 不会做同样的事情。幸运的是，CVErr(xlErrNull) 有效。
回答by Ronan Paix?o
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 COUNTAstill counts those cells.
在单元格中，它会显示为#VALUE!，这有利于通过电子表格中的公式传播（确保用户知道存在错误），而它不会被某些函数（如COUNT. 请注意，COUNTA仍然计算这些单元格。
回答by user2719206
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)
x = myfunction(a,b)
It worked perfectly.
它工作得很好。
