5

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 ?

0m3r
  • 12,286
  • 15
  • 35
  • 71
griseldataborda
  • 123
  • 1
  • 3
  • 10

4 Answers4

11

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.

Community
  • 1
  • 1
jtolle
  • 7,023
  • 2
  • 28
  • 50
7

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..

so in your example: 13 is being returned for instr(str_Text,"/10').

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)

0m3r
  • 12,286
  • 15
  • 35
  • 71
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • wow, I thought instr just checked to see if a piece of a string is found, if so it returned true....clearly I have still lots too learn. – griseldataborda Nov 07 '11 at 21:59
  • We all start somewhere. use the help function to look up how to use the available functions. Believe it or not they give good examples! – xQbert Nov 07 '11 at 22:02
  • This answer gets the important bit right - test the return from `Instr`. However, the last paragraph is incorrect. See my answer for more. – jtolle Nov 08 '11 at 07:10
1

Instr() return a numeric result: if it's >0 then the tested string contains the string being searched for.

Sub Test()

    Dim str_text As String

    str_text = "SKU1234 $100/10'  $200/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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

The INSTR function will return the index of the sub-string you are trying to find in a string.

So the following will give a numeric value instead of a boolean value: 

INSTR(STR_TEXT,"/10'")

To fix this, use the following which will give a boolean answer which is required by if condition: 

INSTR(STR_TEXT,"/10'") > 0
Regolith
  • 2,944
  • 9
  • 33
  • 50
Vaishali
  • 131
  • 9