“Truthy” and “Falsy” Values

Tweet
Share
Share
0 Shares

VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result:

If Me.MyBitField.Value = True Then
  'It is true
Else 
  'It is false
End If

At the first glance, it seems expected that if the bit field is set to 1, it would succeed. But in fact, it wouldn’t because True is equivalent to -1, not 1. Unless we convert the field into a boolean, it would be actually a numeric comparison, not a boolean comparison. Thus, if the field contains anything but -1, the code will evaluate as false.

One way might be to fix this by wrapping this with a CBool conversion:

If CBool(Me.MyBitField.Value) = True Then

However, the problem is that CBool() is not null-safe, so CBool(Null) will yield a runtime error about an invalid use of null.

There is much better way:

If Me.MyBitField.Value Then
  'It is true
Else
  'It is false
End If

In that case, VBA will do the expected thing, even for a Null value which in this context is a falsy value and thus jump to the Else branch as expected.

For evaluating the “falsy” result, this is best done this way:

If Me.MyBitField.Value = False Then

Unlike True, this works because False can be only exactly one value – 0 – and any other value is not a “falsy” value.

But note that if the value is Null, this too jumps to the Else branch, too. If that is problematic, you can use Nz()

If Nz(Me.MyBitField.Value, False) = False Then

Related Posts