Ggtcf

Why does (Not 1) evaluate as -2? I would expect it to evaluate as 0.

VBA/VBScript does not have real logical operators (AND, OR, NOT). The logical operators you see are actually bitwise operators, and that's all you get. VBA plays some games with the True and False values so this works most of the time, but occasionally you'll find a "gotcha".

In this case, instead of If Not InStr() Then you have to write If InStr() <= 0 Then.

Instead of If InStr() Then you have to write If InStr() > 0 Then

In other words: InStr() returns a number. Don't try to treat it like a boolean.

1 is not the integer representation of True, -1 is.

Debug.Print CInt(True) 'prints -1
Debug.Print CInt(False) 'prints 0

Boolean operators (Not, And, Or, XOr) behave as "logical operators" when their operands are Boolean values. When they're not, they behave as "bitwise operators" but the truth is, they're always bitwise.

The integer value 1 converts to the Boolean value True only because True is defined as Not False, which means any non-zero value is a Boolean True. But you only get the correct/expected logical behavior when you use -1 for True.

An Integer is represented by 16 bits, so 1 is this:

0000 0000 0000 0001

This makes Not 1 this:

1111 1111 1111 1110

The sign bit is on, so the value is negative - trim the insignificant digits and you get:

10

Which is the binary representation for 2. Hence, Not 1 is -2.

Inversely, -1 would be:

1111 1111 1111 1111

And Not -1 is thus:

0000 0000 0000 0000

As the other answers have already explained about the why, I want to focus on the coding practices.

As you found out, Not x is not necessarily what you want. In practice you want more like Not CBool(x). However, CBool() can throw errors - for example, CBool(Null) yields error 91 (invalid use of null).

It might be claimed that you can avoid this by strong-typing your variables but even not using a Variant does not guarantee that in an expression a Boolean will stay a Boolean. Example:

?typename(true and 0)
Integer

In practice, it's too easy to accidentally allow VBA to do the voodoo implicit conversions for you, so for that reasons, altering coding habits might suit you better.

For testing truthy values, you want expressions like:

If x Then

And for falsy values, you want expressions like:

If x = False Then

Those works regardless of the type of the x, whether it's an expression or not and thus is said to be much more consistent/predictable in its behavior compared to the If x = True Then or If Not x Then. By adopting this code habit, you can help avoid creating subtle bugs arising from accidental conversion away from Boolean types and getting a bitwise operation rather than logical operation.

For assignments, using a Boolean variable will work in ensuring that it's consistently coerced into either True or False and not some random numbers.