vba 即使有公式，如何检查单元格是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15106172/
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
how to check if the cell is blank even if it has formulae
提问by Alvi John
Application.WorksheetFunction.SumIf(_
sht_store.Range(Cells(k, 12), (k, lcol)),_
" <>",_
sht_store.Range(Cells(5, 12), Cells(5, lcol)))
I wrote this code to sum up the values if the cell is nonblank .. However this is not working since the cell may be blank but still might have formulae in it.
如果单元格是非空白，我编写了这段代码来总结这些值。但是这不起作用，因为单元格可能是空白但仍然可能有公式。
I cant change any other code in the module.. Is there any way i can give some other condition so as to sum up the non blanks(instead of "<>") ?
我无法更改模块中的任何其他代码。
回答by bonCodigo
In VBA you can use
在 VBA 中，您可以使用
IsEmptyfor empty cells- To check if a Cell has formula,
IsEmpty对于空单元格- 要检查单元格是否有公式，
Code:
代码：
Option Explicit
Function IsFormula(ByRef wscell As Range) As Boolean
IsFormula = wscell.HasFormula
End Function
In Excel you may use
在 Excel 中，您可以使用
IsBlankfor empty cells
IsBlank对于空单元格
Thus, in your case check this out:
因此，在您的情况下，请查看以下内容：
- Check if the Cell has a formula
- Then check if it IsNull, without spaces.
- 检查单元格是否有公式
- 然后检查它是否为空，没有空格。
Afterwards you may get going with your SumIF. Regardless of the formula, this will return if cell is really empty/null or not.
之后，您可以开始使用您的SumIF. 不管公式如何，如果单元格真的为空/空，这将返回。
Function izNull(ByRef rng As Range) As Boolean
If Trim(rng.Value) = "" Then
izNull = True
End If
End Function
回答by Pepys
I got it to work with using .Textproperty:
我让它与使用.Text属性一起工作：
Cells([rowindex],[columnindex]).Text = ""
Cells([rowindex],[columnindex]).Text = ""
