Excel VBA instr if statement false

Question

I'm trying to not look for value 2, however "shouldn't happen" gets shown rather then the else, "ok".

If Not InStr("1, 2, 3", "2") Then
    MsgBox ("shouldn't happen")
Else
    MsgBox ("ok")
End If

We know the value is within the string. yet for some reason the "not" is not working. Does anyone know why?

Read the documentation on MSDN or on your online help. Instr returns the position as an integer, not a boolean. — d-stroyer
– d-stroyer, Commented Jul 11, 2013 at 14:25

Alex K. · Accepted Answer · 2013-07-11 13:54:32Z

7

Thats because

?InStr("1, 2, 3", "2")
 4

and

?not 4
-5 // bitwise not of 4

which is a truthy value (cbool(-5) = true), so instead you need to:

if InStr("1, 2, 3", "2") = 0 then
  // not found
else 
  // found

answered Jul 11, 2013 at 13:54

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1