vba 检查单元格值类型的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13536186/
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
Macros to check cell's value type
提问by Daniel Genov
I have the following question. I want to check with macros if a certain cell within range of cells in Excel contains date values (short date i.e. 12.3.2012) or specific word.
我有以下问题。我想用宏检查 Excel 中单元格范围内的某个单元格是否包含日期值（即短日期12.3.2012）或特定单词。
I will try to explain myself with the following example code:
我将尝试用以下示例代码来解释自己：
Private Sub typedata()
Dim x, y
For x = 12 To 13
For y = 16 To 71
If isDate(Cells(x, y)) <> True then
MsgBox "Please enter correct data"
Exit Sub
elseIF Cells(x,y) <> "certain word" Then
MsgBox "Please enter correct data"
Exit Sub
End If
Next y
Next x
End Sub
I will appreciate any help to find the correct code.
我将不胜感激任何帮助找到正确的代码。
回答by Alan K
First, I suspect (but am not certain) that you have your x's and your y's around the wrong way. The Cells function uses the arguments (Row, Column) and it would be rare to have a really short data range (rows 12 to 13) but a really wide one (columns 16 to 71). (Your code may be right, I'm just noting that it's unusual and something that you may want to check.)
首先，我怀疑（但不确定）您的 x 和 y 绕错了方向。Cells 函数使用参数 (Row, Column)，很少有数据范围非常短（第 12 到 13 行）但非常宽（第 16 到 71 列）的情况。（您的代码可能是正确的，我只是注意到它很不寻常，您可能想检查一下。）
What you're really looking for is the And operator, though one other thing to note is that the certain word test will be case sensitive. I normally therefore compare by converting both to upper case.
您真正要寻找的是 And 运算符，但要注意的另一件事是某些单词测试将区分大小写。因此，我通常通过将两者都转换为大写来进行比较。
Finally, you may want to tell the user where exactly the problem is.
最后，您可能想告诉用户问题究竟出在哪里。
Also I do recommend declaring your variables as a type to avoid the variant to integer conversion cost. I always use Longs rather than integers in VBA (not VB.Net) because Ints get internally converted to longs anyway.
此外，我建议将您的变量声明为一种类型，以避免变体到整数的转换成本。我总是在 VBA（不是 VB.Net）中使用 Longs 而不是整数，因为 Ints 无论如何都会在内部转换为 longs。
This is quick, dirty and only marginally tested but should point you in the right direction:
这是快速、肮脏且仅经过少量测试，但应该为您指明正确的方向：
Private Sub typedata()
Dim x As Long, y As Long
For x = 12 To 13
For y = 16 To 71
If IsDate(Cells(x, y)) <> True And UCase(Cells(x, y)) <> UCase("certain word") Then
MsgBox "Please enter correct data into cell " & Cells(x, y).Address
Exit Sub
End If
Next y
Next x
End Sub
