VBA if with InStr

Question

I'm trying to write a VBA code with if the i get an error.

I want the code to check if one of the values ("S925,S936,S926,G") is not on cell 10.

Sub checklist()

 Dim x
Dim LineType

NumRows = Cells(Rows.Count, "j").End(xlUp).Row

For x = 2 To NumRows

    If LineType = "G" Then
        If Not InStr("S925,S936,S926,G", cellsCells(x, 10).Value) Then
       cells     Cells(x, 52).Interior.Color = rgbCrimson
       cells     Cells(x, 52).Value = "G"


 End If
      End If


    End If

Next x


End Sub

See [this answer](http://stackoverflow.com/a/11112615/4604845) for a solution to a slightly different question that uses `InStr` to essentially solve the problem in a way that is identical to your approach. — Vegard, Oct 06 '16 at 07:13
what kind of error? you only removed part of the loop so your example wouldn't even compile. — arcadeprecinct, Oct 06 '16 at 07:15
where is your `For x = ...` ? you have only the closing statement `Next x` — Shai Rado, Oct 06 '16 at 07:24
sorry, i forgot to add this: 'Sub checklist() Dim x Dim LineType NumRows = cells(Rows.Count, "j").End(xlUp).Row For x = 2 To NumRows' — Dan Helman, Oct 06 '16 at 07:30
What the hell happened in that last edit? It doesn't make any sense. Please describe your error if you want help. Check if you have a variable or function called `cells`. — arcadeprecinct, Oct 06 '16 at 08:10

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

This won't cause an error but it will cause issues with your program so I'll explain it.

InStr doesn't return a Boolean but the index of the first occurrence of the search string. If the string isn't found it returns 0. For example InStr("12345", "23") will return 2.
Because everything except 0 is cast as True, something like If Instr(....) Then will perform as expected.

However if you use If Not InStr(....) Then something else can/will happen

If Not InStr("12345", "23") Then
    Debug.Print "test evaluated as True!"
End If

this prints test evaluated as True! even though "23" is contained in "12345". This is not because InStr returned False though. We can replace the InStr expression with 2 to better understand:

Debug.Print 2               '2 (duh)
Debug.Print CBool(2)        'True (2 converted to Boolean)
Debug.Print Not 2           '-3
Debug.Print CBool(Not 2)    'True (-2 converted to Boolean)

Wy is Not 2 evaluated as -3? That's because the 2 isn't converted to Boolean before Not is applied but the Not is applied bit-wise to the 2, which means every bit is flipped. So 2 (0010) becomes 1101 which is -3 because the computer uses the two's complement to express negative numbers. (Actually more bits are used for Integer but it works the same.) As -3 is not 0, it will be converted to True. Since Not 0 will also be evaluated as True (0000 will be converted to 1111 which is -1 as two's complement) the expression Not InStr(...) will always be evaluated as True.

This bit-wise behavior isn't noticed when working with Booleans because they are represented as 0000 and 1111 internally. This also becomes apparent like this:

Debug.Print 1 = True        'False
Debug.Print CBool(1) = True 'True
Debug.Print -1 = True       'True
Debug.Print CBool(-1) = True'True
Debug.Print CInt(True)      '-1 (True converted to Integer)

As you can see here, the True is converted to an Integer rather than the Integer being converted to a Boolean for the = comparison.

Long explanation, short fix: Use If InStr(...) > 0 Then instead of If InStr(...) Then and If InStr(...) = 0 Then instead of If Not InStr(...) Then.

PS: This can also cause confusing behavior if you combine two InStr tests with And because And will be applied bitwise as well.

VBA if with InStr

1 Answers1

Linked