I asked a question on Stack Overflow yesterday about some strange behavior I observed with the VBComponent.Saved property:
The problem, as I saw it, is that using the
CBool() function alone is not enough to guarantee that a value will be coerced into a safe and reliable Boolean. What do I mean by a "safe and reliable Boolean"? I mean one that is stored as either a 0 or -1.
If the boolean value is not stored as a 0 or -1, then it's prone to causing subtle bugs. I'm talking about bugs like this where applying Not to a True value results in...a True value:
x = 1 ?CBool(x) True ?CBool(Not x) True
This occurs because Not is a bitwise operator and any number that is not zero is considered True.
CBool() to the rescue?
The problem in the example above is that we applied the Not operator directly to the integer value 1. This converted the value of 1 to -2 instead of 0. When we converted -2 to Boolean, it then returned True:
x = 1 ?Not x -2 ?CBool(-2) True
But, what if we converted 1 to Boolean before we applied the Not operator? This will work because CBool(1) will force the value to be stored as -1:
x = 1 ?CBool(x) True ?Not CBool(x) False ?CInt(CBool(x)) -1
CBool() works...unless it doesn't
This brings me back to the question I posted to Stack Overflow. I was working with the VBComponent.Saved property. The .Saved property is a Boolean. But--and this is a big "but"--the True value of the .Saved property is a 1:
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(1) ?VBComp.Saved True ?TypeName(VBComp.Saved) Boolean ?CInt(VBComp.Saved) 1
What's the big deal? Only this:
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(1) ?VBComp.Saved True ?Not VBComp.Saved True
OK, that's not ideal. It can lead to all sorts of hard-to-find bugs. But we know how to fix this, right? We'll just coerce it to Boolean before applying the Not operator. Just like we did earlier. Here we go:
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(1) ?CBool(VBComp.Saved) True ?Not CBool(VBComp.Saved) True
Oh no! CBool() works until it doesn't! What the heck is going on here? Let's go check the VBA spec for some sort of explanation.
Let-coercion to and from Boolean
This is the fancy name for what's actually going on here. When we convert between types in VBA--either explicitly via CBool() or implicitly via context--the VBA language specification provides rules that govern the operation. Here are the rules when coercing to and from Boolean types:
When we are converting from a Boolean to a Boolean, the language merely copies the source value. That is, applying CBool() to a Boolean has no effect.
And that has real consequences when we're dealing with a Boolean data type that is stored as something other than 0 or -1.
A reliable approach
So, given what we know, how can we coerce any value to a Boolean and ensure that it is only represented by a 0 or -1? For any types but Booleans, we just need to coerce it to boolean using the CBool() function. But we've already established that won't work for Booleans.
Here's my proposed three-step process:
- Explicitly coerce the value to Boolean (in case it's not already)
- Explicitly coerce the value to Integer (in case the original value was a boolean)
- Explicitly coerce the value to Boolean
Here's the very ugly code that makes this happen:
Let's test this approach on a few values:
x = 111222333 ?CBool(CInt(CBool(x))) True ?Not CBool(CInt(CBool(x))) False
Note that without the inner CBool() the above code would have resulted in an overflow when we coerced
x to an integer.
Now, let's revisit the original problem, VBComp.Saved:
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(1) ?CBool(CInt(CBool(VBComp.Saved))) True ?Not CBool(CInt(CBool(VBComp.Saved))) False
Hey, look! It works now!
Booleans in VBA are fraught with hidden risk. The seemingly obvious approach of forcing values to boolean using the CBool() function is not enough to avoid all those risks. The only reliable way I know of is to make it a three-part conversion:
UPDATE: No sooner did I publish this article than Wayne Phillips pointed out a simpler (and almost certainly more performant) approach to achieve the same objective:
>> CBool(CInt(CBool(x)))— EverythingAccess.com (@WaynePhillipsEA) December 30, 2020
What about this instead....
x <> False
Sure enough, Wayne's approach is spot on:
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(1) ?VBComp.Saved <> False True ?Not (VBComp.Saved <> False) False