vba InStr 未找到子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12113845/
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
InStr not finding substring
提问by thedeepfield
I have the following code:
我有以下代码：
Columns("F:F").Select
For Each cell In Selection
Select Case cell
Case InStr(1, cell, ",") <> 0
MsgBox ("found")
Case IsEmpty(cell) Or Null
MsgBox ("empty")
Case Else
Stop
End Select
Next
In column F:F I have the following in order: "Marc Jacobs", "", "Renolds, Bob"InStr is not finding any of the proper case statements.
在 F:FI 列中，按顺序排列如下："Marc Jacobs", "", "Renolds, Bob"InStr 未找到任何正确的 case 语句。
- For "Marc Jacobs", I get the Case Else call (Correct call)
- For "", I get the found message (Should be the empty message)
- For "Renolds, Bob", I get the Case Else call (Should get the found message)
- 对于“Marc Jacobs”，我收到了 Case Else 电话（正确电话）
- 对于“”，我得到找到的消息（应该是空消息）
- 对于“Renolds，Bob”，我收到了 Case Else 电话（应该收到找到的消息）
Whats going on here?
这里发生了什么？
回答by tgolisch
The way that you are using the Select Case syntax seems to be the problem. Each of your Cases contains a calculation. So, VB will do the calculation for each of the Cases before it does the "Select Case" comparisons.
您使用 Select Case 语法的方式似乎是问题所在。您的每个案例都包含一个计算。因此，VB 将在进行“选择案例”比较之前对每个案例进行计算。
For example: for your first loop, the cell="Renolds, Bob". So your first Case condition will evaluate InStr(1,cell,",") as 8 and it will evaluate (8<>0) to True. However, "Renolds, Bob" does not equal (InStr(1,cell,",") <> 0) 'which equals True. Oddly, when VBA converts "" to a boolean, so it can compare them, "" converts to True.
例如：对于您的第一个循环，单元格 =“Renolds, Bob”。因此，您的第一个 Case 条件会将 InStr(1,cell,",") 评估为 8，并将 (8<>0) 评估为 True。但是，"Renolds, Bob" 不等于 (InStr(1,cell,",") <> 0) '，它等于 True。奇怪的是，当 VBA 将 "" 转换为布尔值，以便可以比较它们时，"" 转换为 True。
What you probably wanted to write was
你可能想写的是
For Each cell in Selection
If InStr(1, cell, ",") <> 0 Then
MsgBox("found")
ElseIf IsEmpty(cell)
MsgBox("empty")
Else
Stop
End If
Next
回答by xll
You can use Select...Case statement too:
您也可以使用 Select...Case 语句：
For Each cell In Selection
Select Case True
Case InStr(1, cell, ",") <> 0
MsgBox ("found")
Case IsEmpty(cell)
MsgBox ("empty")
Case Else
Stop
End Select
Next
