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.
1 2 3 4 5 | SELECT * FROM NameOfTable WHERE NameOfColumn IS NOT NULL; |
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.
When the submit button is pushed, the data are appended to a database. The fields in the database are defined as follows.
| Field name | Field type | |
|---|---|---|
| Index | numeric | Key, generated automatically |
| NameFirst | text | |
| NameLast | text | |
| Score | numeric | |
| Comment | text |
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?
| Index | NameFirst | NameLast | Score | Comment |
|---|---|---|---|---|
| 1 | null | null | null | null |
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.
| Index | NameFirst | NameLast | Score | Comment |
|---|---|---|---|---|
| 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.
1 2 3 | Dim V As Variant V = 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.
1 2 3 4 5 6 | Dim V As Variant V = Null MsgBox VarType(V) 'shows 1 MsgBox TypeName(V) 'shows Null |
There is a special function IsNullto check whether or not the variable of the type Variant contains Null.
1 2 3 4 5 6 7 | Dim V As Variant V = Null If (IsNull(V)) Then MsgBox "V contains Null" 'shows V contains Null End If |
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 value | How to check for the special value | Meaning | |
|---|---|---|---|
Empty | If (IsEmpty(V)) Then | A 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. | |
Nothing | If ((V Is Nothing)) Then | The Variant variable contains an object variable that has not been assigned to an object reference. | |
Null | If (IsNull(V)) Then | The 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | Public Sub Example() Dim JSONString As String JSONString = "{""Person1"":""Female"",""Person2"":""Female"",""Person3"":""Male"",""Person4"":null,""Person5"":""Male""}" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONString) If (JSON.IsValid()) Then MakeCalculation D Else MsgBox JSON.ShowWhyInvalid() End If Set D = Nothing Set JSON = Nothing End Sub Public Sub MakeCalculation(ByRef D As Dictionary) Dim NumberOfMen As Long NumberOfMen = 0 Dim NumberOfWomen As Long NumberOfWomen = 0 Dim Index As Long Index = 1 Do While D.Exists("Person" & CStr(Index)) Select Case D.Item("Person" & CStr(Index)) Case "Male" NumberOfMen = NumberOfMen + 1 Case "Female" NumberOfWomen = NumberOfWomen + 1 End Select Index = Index + 1 Loop MsgBox "Number of men: " & CStr(NumberOfMen) & Chr(10) & "Number of women: " & CStr(NumberOfWomen) 'shows Number of men: 2 Number of women: 2 End Sub |
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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Public Sub MakeCalculation(ByRef D As Dictionary) Dim NumberOfMen As Long NumberOfMen = 0 Dim NumberOfWomen As Long NumberOfWomen = 0 Dim Index As Long Index = 1 Dim Gender As String Gender = "" Do While D.Exists("Person" & CStr(Index)) Gender = D.Item("Person" & CStr(Index)) Select Case Gender Case "Male" NumberOfMen = NumberOfMen + 1 Case "Female" NumberOfWomen = NumberOfWomen + 1 End Select Index = Index + 1 Loop MsgBox "Number of men: " & CStr(NumberOfMen) & Chr(10) & "Number of women: " & CStr(NumberOfWomen) End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Public Sub MakeCalculationBis(ByRef D As Dictionary) Dim NumberOfMen As Long NumberOfMen = 0 Dim NumberOfWomen As Long NumberOfWomen = 0 Dim Index As Long Index = 1 Dim Gender As String Gender = "" Do While D.Exists("Person" & CStr(Index)) If (IsNull(D.Item("Person" & CStr(Index)))) Then Gender = "NA" Else Gender = D.Item("Person" & CStr(Index)) End If Select Case Gender Case "Male" NumberOfMen = NumberOfMen + 1 Case "Female" NumberOfWomen = NumberOfWomen + 1 End Select Index = Index + 1 Loop MsgBox "Number of men: " & CStr(NumberOfMen) & Chr(10) & "Number of women: " & CStr(NumberOfWomen) End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Public Sub ExampleBis() Dim JSONString As String JSONString = "{""Person1"":""Female"",""Person2"":""Female"",""Person3"":""Male"",""Person4"":null,""Person5"":""Male""}" Dim JSON As cJSON Set JSON = New cJSON JSON.SetHowToConvertJSONNullToVBA UseNullString Dim D As Dictionary Set D = JSON.Deserialize(JSONString) If (JSON.IsValid()) Then MakeCalculation D Else MsgBox JSON.ShowWhyInvalid() End If Set D = Nothing Set JSON = Nothing End Sub |
The best option is the one that fits your needs best.