vba Instr 条件不适用于 2 个或更多条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8042744/
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 condition doesn't work with 2 or more conditions
提问by griseldataborda
I have the following INSTR condition in Excel VBA which doesn't work (all the time)
我在 Excel VBA 中有以下 INSTR 条件，它不起作用（一直）
STR_TEXT="SKU1234 0/10' 0/20'" ' < example string
IF INSTR(STR_TEXT,"/10'") AND INSTR(STR_TEXT,"/20'") THEN
' code
ELSE
' code
END IF
For some obscure reason, it seems like it cannot check for both conditions so the first IF, even if both condition match, doesn't seem to work and goes to ELSE.
出于某种晦涩的原因，它似乎无法检查两个条件，因此第一个 IF，即使两个条件都匹配，似乎不起作用并转到 ELSE。
The following does work:
以下确实有效：
STR_TEXT="SKU1234 0/10' 0/20'" ' < example string
IF INSTR(STR_TEXT,"/10'") THEN
IF INSTR(STR_TEXT,"/20'") THEN
' code
END IF
ELSE
' code
END IF
As you can see, if I separate the conditions on the first IF, it works. But I would prefer to have both conditions in same IF, as code is 'cleaner'.
如您所见，如果我将第一个 IF 上的条件分开，它就会起作用。但我更喜欢在同一个 IF 中使用两个条件，因为代码更“干净”。
Anyone knows why and/or how to fix it without having to put an IF inside another IF ?
任何人都知道为什么和/或如何修复它而不必将 IF 放入另一个 IF 中？
回答by jtolle
The other answers point out the most important thing, which is that InStractually returns the numeric positionof one string in another (or 0 if the desired string isn't found). As they say, you should be testing the condition<result> > 0in your Ifstatement. I'll just address what the reason is behind your observation that your test "doesn't work (all the time)". It's a nice chance to revel in some ancient I-<3-BASIC awesomeness.
其他答案指出了最重要的事情，即InStr实际上返回一个字符串在另一个字符串中的数字位置（如果找不到所需的字符串，则返回0）。正如他们所说，您应该在语句中测试条件。我将说明您观察到您的测试“不起作用（一直）”背后的原因。这是一个陶醉于一些古老的 I-<3-BASIC 令人敬畏的好机会。<result> > 0If
What's going on is that, in this case(see edit at the bottom for more) VBA's Andoperator (and Or, etc.) is actually a bitwiseoperator, not a logicalone. That is, if you pass it two integer operands, it will do a bit-by-bit And, and return back the resulting integer. For example 42 And 99evaluates to 34, because (in binary) 0101010 And 1100011is 0100010.
发生的事情是，在这种情况下（有关更多信息，请参阅底部的编辑）VBA 的And运算符（和Or等）实际上是按位运算符，而不是逻辑运算符。也就是说，如果您向它传递两个整数操作数，它将逐位执行And，并返回结果整数。例如42 And 99评估为34，因为（二进制）0101010 And 1100011是0100010。
Now, normally, if you use VBA Booleanvalues, Andworks like a logical operator. This is because in VBA, the constant Trueis equal to the numeric -1, and Falseis equal to the numeric zero. Because VBA represents -1as a binary number with all bits set, and zero as a binary number with all bits cleared, you can see that binary operations become equivalent to logical operations. -1 And <something>always equals the same <something>. But if you're just passing any old numbers to And, you'll be getting back a number, and it won't always be a numeric value that is equal to the constants Trueor False.
现在，通常，如果您使用 VBABoolean值，则其And工作方式类似于逻辑运算符。这是因为在 VBA 中，常量True等于 numeric -1，并且False等于数字零。因为 VBA 表示-1为所有位都设置的二进制数，而零表示为所有位都清除的二进制数，所以您可以看到二进制运算等同于逻辑运算。-1 And <something>始终等于相同<something>。但是，如果您只是将任何旧数字传递给And，您将返回一个数字，并且它并不总是等于常量True或的数值False。
Consider a simple example (typed in the Immediate window):
考虑一个简单的例子（在立即窗口中输入）：
x="abc"
?Instr(x,"a")
1
?Instr(x,"b")
2
?Instr(x,"c")
3
?(Instr(x,"a") and Instr(x, "b"))
0
?(Instr(x,"a") and Instr(x, "c"))
1
Now recall that VBA's Ifstatement treats any non-zero numeric argument as being the same as True, and a zero numeric argument as being the same as False. When you put all this together, you'll find that a statement of your example form:
现在回想一下，VBA 的If语句将任何非零数字参数视为与 相同True，将零数字参数视为与 相同False。当你把所有这些放在一起时，你会发现你的示例表单的声明：
IF INSTR(STR_TEXT,"/10'") AND INSTR(STR_TEXT,"/20'") THEN
will sometimes pick the first condition and sometimes the second, depending on just what is in the searched string. That's because sometimes the bitwise Andoperation will return zero and sometimes it will return non-zero. The exact result will depend on the exact positions of the found strings, and this clearly isn't what you'd expect. So that'swhy the advice you've already gotten matters in the details.
有时会选择第一个条件，有时会选择第二个条件，具体取决于搜索字符串中的内容。那是因为有时按位And运算会返回零，有时会返回非零。确切的结果将取决于找到的字符串的确切位置，这显然不是您所期望的。所以这就是为什么你已经得到的建议在细节上很重要。
EDIT: As pointed out by Hugh Allen in this comment:
编辑：正如 Hugh Allen 在此评论中所指出的：
Does the VBA "And" operator evaluate the second argument when the first is false?
当第一个参数为假时，VBA“And”运算符是否评估第二个参数？
VBA's Andoperator does actually return Booleanvalues of both of it's operands are Boolean. So saying that it's a bitwise operator is not strictly correct. It's correct for this problem though. Also, the fact that it canact as a bitwise operator does mean that it can't act like a "normal", purely logical, operator. For example, because it must evaluate both operands in order to determine if they are numbers or not, it can't short-circuit.
VBA 的And运算符实际上返回Boolean它的两个操作数的值是Boolean。所以说它是一个按位运算符并不严格正确。不过对于这个问题是正确的。此外，它可以充当按位运算符的事实确实意味着它不能像“正常”的、纯逻辑的运算符一样运行。例如，因为它必须评估两个操作数以确定它们是否是数字，所以它不能短路。
回答by xQbert
EXMAPLE:
示例：
if instr(str_Text,"/10'") > 0 AND instr(str_text,"/20'") > 0 then
What Tim is saying is that the instr function returns the position in the string of the first instance of the string being searched for..
Tim 所说的是 instr 函数返回正在搜索的字符串的第一个实例在字符串中的位置。
so in your example: 13 is being returned for instr(str_Text,"/10').
所以在你的例子中：为 instr(str_Text,"/10') 返回 13。
When VBA reads your version instr(str_text,"/10;") (without the >0) then it sees that the result is not 1 (which means true) so it always hits the else)
当 VBA 读取您的版本 instr(str_text,"/10;") （没有 >0）然后它看到结果不是 1（这意味着真）所以它总是命中其他）
回答by Tim Williams
Instr() return a numeric result: if it's >0 then the tested string contains the string being searched for.
Instr() 返回一个数字结果：如果 >0，则测试字符串包含正在搜索的字符串。
Sub Test()
Dim str_text As String
str_text = "SKU1234 0/10' 0/20'" ' < example string
If InStr(str_text, "/10'") > 0 And InStr(str_text, "/20'") > 0 Then
MsgBox "Both"
Else
MsgBox "Only one, or none"
End If
End Sub
回答by Vaishali
The INSTRfunction will return the index of the sub-stringyou are trying to find in a string.
该INSTR函数将返回sub-string您试图在string.
So the following will give a numeric value instead of a boolean value:
所以下面将给出一个数值而不是 a boolean value：
INSTR(STR_TEXT,"/10'")
To fix this, use the following which will give a booleananswer which is required by if condition:
要解决此问题，请使用以下内容，这将给出booleanif 条件所需的答案：
INSTR(STR_TEXT,"/10'") > 0
