VBA policy on double sided inequalities?

Question

Was fooling around with trying to reduce the length of the code so that it gives off fewer headaches to look at and debug, and I came across this curious little fact:

Debug.Print 5<9<8 'returns "True"

At first I thought this was because it just checked the first set, but then I found that

Debug.Print 5<4<8 '*also* returns "True"

Does VBA interpret this kind of triple inequality as an Or statement? I can't imagine why someone would choose to make that the interpretation VBA makes because it's almost certainly the less used option, but I struggle to think of another explanation.

Also, what is a quick and pretty way of writing If 5 < X < 8 Then (to use sample numbers), without having to resort to endless And statements, ie If 5 < x And X < 8 Then? It's okay for one statement, but the doubling of length adds up quick, especially since variables aren't typically named X.

Edit: okay, it's certainly not an Or because VBA also says that Debug.Print 8<6<2 is True. What on earth is it thinking?

Answer 1

The other answers covered up nicely the first part of your question, but didn't satisfactorily cover up the second part of it, i.e. What is a quick and pretty way of writing If 5 < X < 8 Then (to use sample numbers), without having to resort to endless And statements, i.e. If 5 < x And X < 8 Then?

There are two ways. The first:

Select Case X
    Case 5 To 8
    ...
End Select

Here, the value before the To keyword must be the smaller value of the two. Also note that while this will work for integers, I have no idea if it works for types like Double and such (I suspect it won't though).

The second way, which works irrespective of whether the interval bounds are integers or not, is not necessarily shorter, but it evaluates things in a single comparison:

If Sgn(x - 5) + Sgn(x - 8) = 0 Then ...

This is an interesting way of evaluating whether a value is between some bounds, because it can also provide information on whether the value is equal to one of those bounds or is "outside" them (and on which "side" it is). For example, on a -∞..0..+∞ axis:

• if x = 4, the expression above is -2, thus x is to the left of the (5..8) interval
• if x = 5, the expression above is -1, thus x is the left bound of the (5..8) interval
• if x = 6, the expression above is  0, thus x is inside the (5..8) interval, i.e. between its bounds
• if x = 8, the expression above is  1, thus x is the right bound of the (5..8) interval
• if x = 9, the expression above is  2, thus x is to the right of the (5..8) interval

Of course, if you want to include the bounds in the interval, say, test If 5 <= x And X <= 8 Then, the comparison above becomes If Abs(Sgn(x - 5) + Sgn(x - 8)) < 2 Then ..., which is another shortcut to check if the expression is -1, 0 or 1.

In the end, none of the ways above are as short as a Between(x, 5, 8) hypothetical function, but at least they are alternatives to the "classical" method.

Answer 2

Looking at it from a parse tree perspective might shed more light about why it works that way.

Excluding whatever instruction comes after the THEN token, the parse tree for If 5 < X < 8 Then might look something like this (quite simplified):

The comparison operators being a binary operator, there's an expression on either side of it, and in order to resolve the Boolean expression for the IfBlockStatement, VBA needs to visit the tree nodes in a specific order: because VBA parses expressions left to right, the 5 < X part stands on its own as an expression, and then the result of that expression is used to resolve the {expression} < 8 part of the expression.

So when VBA resolves 5 < X, because that's a ComparisonExpression the result is a Boolean value; when that Boolean value then needs to be compared to the 8 integer literal, VBA performs an implicit type conversion and actually compares CInt({Boolean}) < 8, which will evaluate to True regardless of the result of the first expression, since False converts to 0 and True converts to -1 when expressed as an integer literal, and both are < 8.

These mechanics are built into how the runtime works, so in order to evaluate if X is between 5 and 8, you need to build your expression so that it's parsed as such:

If X > 5 And X < 8 Then

That gives you two distinct expression trees joined by a LogicalAndOperator, which then works off a valid Boolean expression on either sides.

Answer 3

It's to do with the way VBA evaluates expressions and implicit conversion. The first part of the equation is evaluated and the result stored as a numeric value (the boolean is implicitly converted to an integer)

(well.... technically a boolean is just an integer, but we'll just go along like so...)

'// True = -1
'// False = 0

Debug.Print 5 < 9 < 8

Debug.Print CInt(5 < 9) '// Prints -1

Debug.Print -1 < 8 '// = True

Which is why the following gives "False" instead:

Debug.Print 5 < 9 < -1

Because

Debug.Print Cint(5 < 9) '// True = -1
Debug.Print -1 < -1 '// False

---

If you want to find out if something is in the middle of two other numbers then you have to use the And operator to force a separate evaluation (either side of the operator is then evaluated and compared logically)

Debug.Print (3 < 5 And 5 < 4) '// False

Answer 4

I have no clue but my educated guess would be that it first evaluates the left side of the equation (5<9) which gives TRUE. Then, it proceeds to evaluate the rest (TRUE<8) and implicitly converts TRUE to its integer value (I believe this to be -1 in VB).

-1<8 -> TRUE

Works with the second case as well since FALSE will convert to 0 and 0<8.

Basically it would have everything to do with implicit conversion of boolean to integer and their respective value in VBA.

Answer 5

5<9<8 = True<8 = True

5<4<8 = False<8 = True