Advanced

Deceptively Complex Booleans

Why is VBA such an optimistic language? Because there's only one way to say "No," but 65,535 ways to say "Yes."

Mike Wolfe

Dec 29, 2020 • 4 min read

What could be simpler than a Boolean? It has only two possible values: True or False. If it's not True, it must be False. If it's False, it can't be True.

Ah, if only it were that simple.

VBA Booleans: 65,535 ways to represent True

Conceptually, a boolean variable is either True or False. But under the hood, the VBA Boolean data type is stored as a 16-bit signed integer.

False is 0. True is -1.

0 is False. -1 is True.

But 1 is also True. -42 is True, too. -32,768 to -1 are all True. As are 1 through 32,767. Any number that's not zero implicitly evaluates to True when treated as a boolean.

Logical operators perform bitwise operations in VBA

What does this section heading mean? Let's break it down.

Logical operators

"Logical operators" are those that perform boolean math. In VBA, those operators are:

You're likely familiar with the first three. The "exclusive or" operator (Xor) is much less common, but can be very handy when appropriate. I have to admit that I have never used the "equivalence" (Eqv) or "implication" (Imp) operators.

Bitwise operations

"Bitwise operations" are those that are applied one bit at a time.

Remember, Booleans are stored in 16-bit signed integers in VBA. So, here's what's happening when we apply the Not operator to a False value:

0000 0000 0000 0000   'False
1111 1111 1111 1111   'True (Not False)

A Boolean in VBA is represented by a "16-bit signed binary 2’s complement integer whose value is either 0 (False) or -1 (True)." A two's complement binary number that is all 1's is always equal to -1. And now you know why "True" values in VBA are represented as -1.

Warning! Warning!

Here's where you need to be careful. As I said at the beginning of this article, any value that is not 0 is considered True. But, if you negate any of those other "True" values besides -1 using the bitwise Not operator, you end up with a different "True" value.

This is much clearer with an example:

0000 1111 0000 1010  'True
1111 0000 1111 0101  'True

A great way to see how this works is to use the "Programmer" mode of the Windows 10 Calculator app.

Here's what this looks like in VBA:

?Not(3850)
-3851

?CBool(3850)
True
?CBool(-3851)
True

Where this is likely to trip you up is if you lose track of what you've got inside your variables. Here's an example:

x = 3850

?CBool(x)
True
?CBool(Not(x))
True

Be extra careful treating boolean-ish values like actual booleans

What do I mean by this? This behavior bit me quite hard not too long ago:

VBA Visual Basic Editor (VBE) object .Saved Boolean bug?

tl;dr: In the code below, the following two conditions both evaluate to True!!! How? Why? If Not IsSaved ThenIf IsSaved Then I’m working with the VBA Visual Basic Editor (VBE) object. The .Saved

Stack Overflowmwolfe02

In rereading the question, I realize that one thing never really got addressed. That's the fact that the property in question (VBComponent.Saved) has a type of Boolean but a value that is neither 0 nor -1 when coerced to Integer.

I tried to reproduce this behavior, but anytime I coerced any number besides 0 or -1 to a boolean, VBA forced the value to either 0 or -1. The situation so vexed me that I posted a follow-up question to Stack Overflow. If you have any insight, I would appreciate you checking it out:

VBComponent .Saved Boolean bug?

Sub VBCompSavedBug() Dim VBComp As Object ’VBComponent With Application.VBE.ActiveVBProject Set VBComp = .VBComponents(1) End With Debug.Print TypeName(VBComp.Saved)