Nullable Types in VBA

The only data type in VBA that can hold a Null value is Variant.  

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.

Some Bugs are Better than Others

Not all bugs are created equal. Avoid the expensive ones by making more of the ones that are easy to find and fix.

No Longer SetMike Wolfe

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 String value.

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.

Acknowledgements

• Technical editing and editorial feedback provided by ChatGPT
• Article excerpt generated with the help of ChatGPT
• Cover image created with Microsoft Designer