vba 如何使用VBA检查单元格是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23656619/
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 a cell is empty using VBA
提问by desperatehipster
I'm trying to make a form on excel so that when people don't fill in certain cells, they will get pop-up error messages. So far, I made this code:
我正在尝试在 excel 上制作一个表格，以便当人们不填写某些单元格时，他们会收到弹出错误消息。到目前为止，我做了这个代码：
If Len(I8) = 0 Then
MsgBox "Please enter Required Date", vbExclamation
Exit Sub
ElseIf Len(D11) = 0 Then
MsgBox "Please enter your name", vbExclamation
Exit Sub
ElseIf Len(H11) = 0 Then
MsgBox "Please enter Work Phone", vbExclamation
Exit Sub
ElseIf Len(L11) = 0 Then
MsgBox "Please enter Home Phone", vbExclamation
Exit Sub
End If
MsgBox "All mandatory fields have been entered", vbExclamation
End Sub
Which seemed to work, but when I added text to the cell I8, the "Please enter Required Date" msgbox popped up anyways.
这似乎有效，但是当我向单元格添加文本时I8，无论如何都会弹出“请输入所需日期”消息框。
I've also tried using Count(I8) = 0, and IfEmpty(I8) = True, but neither were recognized properly in vba.
我也试过使用Count(I8) = 0, 和IfEmpty(I8) = True，但在 vba 中都没有被正确识别。
Also, if I want a pop-up message to appear when a cell is not filled out if they picked "YES" from a drop-down list, what would the function be? So far I've written
另外，如果我希望在未填写单元格时显示弹出消息，如果他们从下拉列表中选择了“是”，那么功能是什么？到目前为止我已经写了
ElseIf Range("D28") = "P16" And Len(Range("M30")) = 0 Then
MsgBox "Please Select whether this file is classified as confidential", vbExclamation
Exit Sub
But I need a defined function and I'm not sure what to pick. Is there a longer way to do it to ensure that both cells are filled out if they pick yes in the first cell?
但我需要一个定义的函数，我不知道该选择什么。如果在第一个单元格中选择是，是否有更长的方法来确保两个单元格都被填写？
回答by neelsg
When you use something like If Len(I8) = 0 Thenin VBA, it will assume I8is a variable. Since you obviously don't have that variable defined, Len(I8)will always be zero.
当你If Len(I8) = 0 Then在 VBA 中使用类似的东西时，它会假设I8是一个变量。由于您显然没有定义该变量，因此Len(I8)将始终为零。
What you should do is use Range("I8")or even ActiveSheet.Range("I8").Valueinstead to get the value that is actually in that cell.
您应该做的是使用Range("I8")或什ActiveSheet.Range("I8").Value至改为获取该单元格中实际存在的值。
So your code should change to:
所以你的代码应该改为：
If Len(Range("I8")) = 0 Then
MsgBox "Please enter Required Date", vbExclamation
Exit Sub
ElseIf Len(Range("D11")) = 0 Then
MsgBox "Please enter your name", vbExclamation
Exit Sub
ElseIf Len(Range("H11")) = 0 Then
MsgBox "Please enter Work Phone", vbExclamation
Exit Sub
ElseIf Len(Range("L11")) = 0 Then
MsgBox "Please enter Home Phone", vbExclamation
Exit Sub
End If
MsgBox "All mandatory fields have been entered", vbExclamation
End Sub
To avoid these kinds of confusing issues, you can specify Option Explicitat the very top of the module. This causes excel to throw an error when you use undeclared variables like that instead of just silently guessing what it should do with it.
为了避免这些令人困惑的问题，您可以Option Explicit在模块的最顶部指定。这会导致 excel 在您使用此类未声明的变量时抛出错误，而不仅仅是默默地猜测它应该用它做什么。
回答by CodeJockey
See comments for why your code doesn't work. The following are statements that will evaluate Trueif your cell is empty:
请参阅注释以了解您的代码为何不起作用。以下是将评估True您的单元格是否为空的语句：
IsEmpty(myCell)
myCell.Value = vbNullstring
Len(myCell) = 0
Enjoy!
享受！
