The Pitfalls of Implicit Boolean Conversion in VBA

Adam Waller, longtime reader and maintainer of my favorite Access version control project, wrote in with the following article idea (shared with permission):

The following lines of code produce the same result:

If Len("Test String") Then Debug.Print "Yes"
If Not Len("Test String") Then Debug.Print "Yes"

Obviously the better approach for the second example is:

If Len("Test String") = 0 Then Debug.Print "Yes"

Using Len is great for testing to see if you have an empty string, but it is not intuitively obvious that you can't add "Not" to flip the result. :-)

The Dangers of Implicit Boolean Coercion

I've written about this phenomenon in the past, including in this article:

Here's an excerpt from that article:

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.

Applying the Concepts to Adam's Sample Code

Let's start by looking at the results of the Len() function:

?Len("Test String")
 11

If we negate this result, we get -12:

?Not Len("Test String")
-12

Keep in mind, though, that the Len() function is evaluated before the negation is applied.  The above code is equivalent to this:

?Not 11
-12

Using Programmer mode in the Windows calculator (refer to article above for details), we can easily see what's happening here.  The decimal representation of the signed 16-bit value 11 in binary is:

0000 0000 0000 1011     '<==  11

When we apply the Not operator, VBA performs a bitwise operation, flipping each individual 0 to 1 and vice versa (negative numbers are counterintuitive in binary):

1111 1111 1111 0100     '<== -12

When we coerce these two integer values to Booleans, we see that both evaluate to True:

?CBool(11)
True
?CBool(-12)
True

However, if we then coerce these Boolean values back to integers, we see that they evaluate to -1:

?CInt(CBool(11))
-1
?CInt(CBool(-12))
-1

Why negative one?  Because its binary representation is all ones:

1111 1111 1111 1111     '<== -1

If we apply the Not operator to -1, we get a genuine False value of 0:

?Not -1
 0
?CBool(0)
False
?CBool(Not -1)
False

Avoiding Danger

There are a few ways to work around this danger.  When it comes to using the Len() function, I simply check to make sure the value is either equal to or greater than zero, depending on the purpose of the conditional.

For example:

If Len(MyString) = 0 Then Debug.Print "MyString is blank"

If Len(MyString) > 0 Then Debug.Print "MyString has characters"

Alternatively, you could explicitly store the results of the calculation in a Boolean variable.  This has the added advantage of creating self-documenting code:

Dim MissingInfo As Boolean

MissingInfo = (Len(MyString) = 0)

If MissingInfo Then
    Debug.Print "MyString is blank"
Else
    Debug.Print "MyString has characters"
End If

Finally, you can use the CBool() function to coerce the result of the Len() call into a Boolean which can then be safely negated, but I feel this is the least readable of all the options:

If Not(CBool(Len(MyString))) Then Debug.Print "MyString is blank"

If CBool(Len(MyString)) Then Debug.Print "MyString has characters"

Acknowledgements

• Article title generated with the help of ChatGPT
• Article excerpt generated with the help of ChatGPT
• Cover image generated with the help of DALL-E 3
• Cover image created with Microsoft Designer