An Article About Nothing

There are at least seven identifiers that convey nothingness in VBA:

  • Nothing
  • Null
  • ""
  • vbNullString
  • vbNullChar
  • Empty
  • Missing

Nothing

Nothing is the starting value of an object variable before it has been initialized (or after it has been explicitly set to Nothing).

Much Ado About Nothing
An in-depth look at the Nothing keyword in VBA: when you need it; when you don’t; and one technique to avoid it altogether.

Null

Null is a value that generally means "unknown."  In VBA, only Variant data types can hold a value of Null.  We use the IsNull() function to test if a variable contains a null value.

Working with Null in Microsoft Access
Let’s explore the many ways to check for, handle, and store Null values. Spoiler alert: the best way to do it varies between VBA and SQL.

""

Two double quotes on their own are used to represent a zero-length string.

vbNullString

The built-in vbNullString constant is similar to "".  The two can be used interchangeably in most situations.  

The key difference is that "" is a real string (meaning it occupies storage in memory) whereas vbNullString is a "null string pointer."  VBA treats it like a string, but it points to a memory address of zero (meaning it does not exist in memory).

The vbNullString Constant in VBA
What is the vbNullString constant in VBA and how does it differ from a simple empty string (”″)?

vbNullChar

Character with an ASCII code of zero.  Equivalent to Chr(0).

This constant is most often used when interacting with C/C++ via API calls, as C-style strings use the null character to indicate the end of a string.

The vbNullChar Constant in VBA
What is the vbNullChar constant in VBA and how does it differ from vbNullString?

Empty

This is the starting value of a Variant variable before it has been assigned a value.  Use the function IsEmpty() to check for this value.

Working with Empty in VBA
A deep dive into the Empty keyword in VBA: why it exists, how to check for it, when it makes sense to check for it, and--most importantly--how NOT to check for it.

Missing

This is the value of an Optional Variant parameter when the calling code does not assign a value to the parameter.  Use the function IsMissing() to check for this value.

The Missing Keyword in VBA
Wondering what the IsMissing() function is all about in VBA? We’ll explore that plus all the ins and outs of the VBA keyword that isn’t: Missing.


This article inspired by A Show About Nothing:

Cover image created with Microsoft Designer

UPDATE [2023-08-11]: Added brief mentions of the IsEmpty() and IsMissing() functions that are used to check for Empty and Missing values, respectively (h/t Colin Riddington).

UPDATE [2023-09-06]: Added links to deep dives on the following topics: Nothing, Null, Empty, and Missing.

UPDATE [2023-11-21]: Added links to deep dives on the following topic: vbNullString and vbNullChar.