Null values for variables in VBA

Question

How do I define a Null string, date or integer in VBA?

I need to be able to assign a Null value to some fields for certain records when data is incomplete or irrelevant, but if I declare a variable as a String, Date or Integer, I get errors when trying to assign a Null value.

Is the only solution to use Variant? If so, then what is the point of all other datatypes in VBA?

Solution

Dim x As Variant
x = Null

Only the Variant data type can hold the value Null.

A Variant is a special data type that can contain any kind of data [...] A Variant can also contain the special values Empty, Error, Nothing, and Null.

The "point" of all the other data types is precisely that they cannot contain any ol' kind of data. This has two advantages that I can think of:

• It's more difficult for the programmer to assign data of an unintended type to the variable by mistake, since this will be detected at compile time. This can help prevent bugs and make things clearer for you and the next person who will be maintaining your code.
• Narrow data types save storage space. Putting integers in a Variant (16 bytes) takes up way more memory than putting them in an Int (2 bytes). This becomes significant if you have large arrays.

Of course, Variants do have their place, as other threads on this site discuss.