As well as testing for VarType zero, a shorthand function exists-IsEmpty-which does the same thing but is more readable.
In early versions of Visual Basic, once a Variant was given a value, the only way to reset it to Empty was to assign it to another Variant that itself was empty. In Visual Basic 5 and 6, you can also set it to the keyword Empty, as follows:
v1 = Empty
I like Empty, although I find it is one of those things that you forget about and sometimes miss opportunities to use. Coming from a C background, where there is no equivalent, isn't much help either. But it does have uses in odd places, so it's worth keeping it in the back of your mind. File under miscellaneous.
Of course, Null is familiar to everyone as that database "no value" value, found in all SQL databases. But as a Variant subtype it can be used to mean no value or invalid value in a more general sense-in fact, in any sense that you want to use it. Conceptually, it differs from Empty in that it implies you have intentionally set a Variant to this value for some reason, whereas Empty implies you just haven't gotten around to doing anything with the Variant yet.
As with Empty, you have an IsNull function and a Null keyword that can be used directly.
Visual Basic programmers tend to convert a variable with a Null value-read, say from a database-to something else as quickly as possible. I've seen plenty of code where Null is converted to empty strings or zeros as soon as it's pulled out of a recordset, even though this usually results in information loss and some bad assumptions. I think this stems from the fact that the tasks we want to perform with data items-such as display them in text boxes or do calculations with them-often result in the all too familiar error 94, "Invalid use of Null."
This is exacerbated by the fact that Null propagates through expressions. Any arithmetic operator (+, -, *, /, \, Mod, ^) or comparison operator (<, >, =, <>) that has a Null as one of its operands will result in a Null being the value of the overall expression, irrespective of the type or value of the other operand. This can lead to some well-known bugs, such as:
v = Null
If v = Null Then
MsgBox "Hi"
End if
In this code, the message "Hi" will not be displayed because as v is Null, and = is just a comparison operator here, the value of the expression v = Null is itself Null. And Null is treated as False in If...Then clauses.
The propagation rule has some exceptions. The string concatenation operator & treats Null as an empty string "" if one of its operands is a Null. This explains, for example, the following shorthand way of removing Null when reading values from a database:
v = "" & v
This will leave v unchanged if it is a string, unless it is Null, in which case it will convert it to "".
Another set of exceptions is with the logical operators (And, Eqv, Imp, Not, Or, Xor). Here Null is treated as a third truth value, as in standard many-valued logic. Semantically, Null should be interpreted as unsure in this context, and this helps to explain the truth tables. For example:
v = True And Null
gives v the value Null, but
v = True Or Null
gives v the value True. This is because if you know A is true, but are unsure about B, then you are unsure about A and B together, but you are sure about A or B. Follow?
By the way, watch out for the Not operator. Because the truth value of Null lies halfway between True and False, Not Null must evaluate to Null in order to keep the logical model consistent. This is indeed what it does.
v = Not Null If IsNull(v) Then MsgBox "Hi" ' You guessed it...
That's about all on Null-I think it is the trickiest of the Variant subtypes, but once you get to grips with how it behaves, it can add a lot of value.
