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.
0. True is
1is also True.
-42is True, too.
-1are all True. As are
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("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
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.
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"