The only data type in VBA that can hold a
Null value is
This is especially important to Access developers, since dealing with
Null values is a big part of working with relational databases. The problem with the
Variant type is that, in addition to holding
Null values, it can also hold every other kind of value in VBA: a string, a date, a currency, etc.
Beginning developers often see this as a good thing. Why even worry about variable types if a
Variant can hold anything? Just make every variable a
Variant and call it a day.
Overuse of Variant Types is a Problem
The main issue is that it turns compile-time errors into runtime errors.
Compile-time errors are detected during the compilation phase, before the program runs. This is beneficial because it allows developers to catch and fix issues early, saving time and preventing software crashes that can disrupt user activity.
Conversely, runtime errors occur during the execution of the program, making them more expensive and difficult to fix. Using the Variant type unwisely can lead to runtime errors, such as type mismatch errors if a Variant holding a String is used where a Date is expected.
Some bugs are simply better than others.
Nullable Types in VB.NET
In an ideal scenario, we could declare a variable that can hold either a
Date value or a
Null value, but wouldn't accept a
VB.NET introduces a solution to this issue with the concept of Nullable types that can hold a specific data type or a
Null value, but nothing else. The syntax for declaring nullable types in VB.NET is quite elegant, as shown below:
Dim InactivatedOn As Date? ' <-- this is VB.NET syntax InactivatedOn = #12/31/1999# 'Valid InactivatedOn = Null 'Valid InactivatedOn = "Y2K" 'Compile error
Nullable Types in VBA
Sadly, VBA does not have an equivalent feature.
In its absence, I recommend including the base data type in a code comment immediately after the Variant keyword when a Nullable type would be beneficial:
Dim InactivatedOn As Variant 'Date InactivatedOn = #12/31/1999# 'Valid InactivatedOn = Null 'Valid InactivatedOn = "Y2K" 'Not a compile error, but likely to ' be a runtime error
This practice can help you and other developers avoid mistakes by reminding you of the intended data type of the variable.
Future Support for Nullable Types in VBA
I could ask you to support a VBA feature request to add Nullable types to the language, but...
...I don't think that would get very far, so I won't even bother.