vba - Instr condition doesn't work with 2 or more conditions

Question

I have the following INSTR condition in Excel VBA which doesn't work (all the time)

STR_TEXT="SKU1234 $100/10'  $200/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.

The following does work:

STR_TEXT="SKU1234 $100/10'  $200/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'.

Anyone knows why and/or how to fix it without having to put an IF inside another IF ?

Answer 1

The other answers point out the most important thing, which is that InStr actually returns the numeric position of one string in another (or 0 if the desired string isn't found). As they say, you should be testing the condition <result> > 0 in your If statement. 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.

What's going on is that, in this case (see edit at the bottom for more) VBA's And operator (and Or, etc.) is actually a bitwise operator, not a logical one. 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 99 evaluates to 34, because (in binary) 0101010 And 1100011 is 0100010.

Now, normally, if you use VBA Boolean values, And works like a logical operator. This is because in VBA, the constant True is equal to the numeric -1, and False is equal to the numeric zero. Because VBA represents -1 as 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 True or 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 If statement 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:

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 And operation 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's why the advice you've already gotten matters in the details.

EDIT: As pointed out by Hugh Allen in this comment:

Does the VBA "And" operator evaluate the second argument when the first is false?

VBA's And operator does actually return Boolean values 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 can act 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.