VBA Null


General

What does the value null mean?

In the context of data, when a field has the value null, it means that we don't know the value for the field. null represents the absence of a value, whatever the type of the field (numeric, text, ...). Only optional fields can have the value null.

As null represents the absence of a value, it doesn't make sense to use the value null in expressions. For example, comparing two fields with null values is meaningless. We can't evaluate whether or not the fields have the same value, because we don't know the values of the two fields. The only meaningful expression is to evaluate if a single field has the value null or not.

For example, in SQL, the following code is meaningful.

It is important to understand that a text field with the value null is not the same as a text field that contains an empty string. Or, that a numeric field with the value null is not the same as a numeric field with the value zero.

It is logical that JSON, being a data exchange format, has built in the value null too.

Example

Imagine the following simple form.

Image showing a small form

When the submit button is pushed, the data are appended to a database. The fields in the database are defined as follows.

Field nameField type
IndexnumericKey, generated automatically
NameFirsttext
NameLasttext
Scorenumeric
Commenttext

Assume the database is empty and that it is possible to submit the form as it is, without filling out anything. In other words, all fields are optional. Imagine you don't fill out the form and submit it anyway. How would the database look like?

IndexNameFirstNameLastScoreComment
1nullnullnullnull

As we don't know the value for all the fields, it is logical to use null for all of them. Note that some of the fields are numeric and others are text. The JSON text that corresponds to this record looks as follows.

{
"Index":1,
"NameFirst":null,
"NameLast":null,
"Score":null,
"Comment":null
}

This is only one option. From the data perpective, it is probably the most correct one. In practice, you will often see a something like this.

IndexNameFirstNameLastScoreComment
1""""0""

All the fields are default values, corresponding to the type of the field. The JSON text that corresponds to this record is given below.

{
"Index":1,
"NameFirst":"",
"NameLast":"",
"Score":0,
"Comment":""
}

From the practical point of view, there is no problem. Both options can work. You just need to realize that the fields now contain real values that correspond to the type of the field, and not null. This might impact database queries. An empty string or zero is not the same as null.

The value Null in VBA

Only variables of the type Variant can have the value Null.

Variables of the type Variant can contain all kind of data. There are two ways to determine the type of data that is in the variable of the type Variant. Either by using the function VarType or TypeName.

There is a special function IsNullto check whether or not the variable of the type Variant contains Null.

Variables of the type Variant can also take other special values. Every special values has its specific meaning. Null is not the same as Empty or Nothing.

Special valueHow to check for the special valueMeaning
EmptyIf (IsEmpty(V)) ThenA value has not yet been assigned to the Variant variable. When the Variant variable is declared, VBA initializes the Variant variable to Empty. Empty behaves as both 0 and the empty string "" in expressions.
NothingIf ((V Is Nothing)) ThenThe Variant variable contains an object variable that has not been assigned to an object reference.
NullIf (IsNull(V)) ThenThe Variant variable has been assigned an unknown value, i.e. the value Null. It is not Empty because a the value Null has been assigned to it.

Be careful, a Variant variable cannot be converted to a string. The editor will not warn you and the code will compile without problems if you try to make the conversion.

From JSON null to VBA Null

The JSON text is deserialized into a VBA dictionary. The JSON value null is not a problem for the VBA dictionary. The VBA dictionary uses a variable of the type Variant to contain the items. As a variable of the type Variant can have the value null, deserializing the JSON value null works fine. Problems may only arise afterwards.In VBA, care is needed when working with variables of the type Variant that have the value Null. For instance, they cannot be converted to a string.

Example of deserializing null

Consider the data in the following JSON text.

{"Person1":"Female","Person2":"Female","Person3":"Male","Person4":null,"Person5":"Male"}

The aim of the VBA code is to count how many persons are male and how many are female.

The VBA code works fine. Skill... or just luck?

Let's make an innocent looking change to the function MakeCalculation. Do you think the VBA code will run?

Image showing VBA error when invalid use of null

The VBA code generates an error. Why?

The deserialization works fine. It is what happens afterwards that is not ok. That VBA code has not been built to handle a Variant with value Null. The implicit conversion to string in Gender = D.Item("Person" & CStr(Index)) is generating the error. VBA cannot convert a Variant with value Null into a string. Note that the editor didn't warn you. Also, the code compiles without problems.

There are two ways to do away with the error.

Option 1

The first option is to adjust the VBA code that is running after the deserializarion. Depending on the complexity of the VBA code, this can be easy or something you want to avoid at all cost. In the example, the adjustment is easy to do.

Option 2

The second option is to change the way in which the JSON value null is deserialized. For example, into a variant containing a string, like this.

The best option is the one that fits your needs best.