A variant is the only type of variable that can store an empty value or a Null value, and is declared thus:
Dim aVar as Variant
Dim aVar2 ' if no type is given then it's declared as a variant
Immediately after declaration a variant stores no value and it is empty.
Also you can assign empty to a variant using aVar = Empty
and it will be empty again.
When a variant stores empty these are both true:
aVar = Empty
IsEmpty(aVar)
You can also set the value of a Variant variable to be Null
aVar = Null
These would now be false
aVar = Empty
IsEmpty(aVar)
However, IsNull(aVar)
would be true.
Null is particularly useful when you use VBA variables to store data that has come from a database table that allows NULL values to be stored in it's fields. In this case it is generally advisable that all the variables need to accommodate storing NULL. So they all need to be variant as this is the only data type that stores NULL.
This is very unfortunate as it would be better to have more strongly typed variables in use.
A variant storing Null is not the same as a variant being Empty. Null indicates that a value was assigned to a variable and the value was Null.
This gets confusing as Null is used by databases to indicate no value has been stored in a field, and most databases allow fields with any datatype to be Null. When a database field stores Null, it is kind of the equivalent to a VBA variant variable having just been declared as “receptacle” for a value and not yet being given a value. The variable, just like the table field is an receptacle without anything in it.
However, if a VBA variant variable is given a Null value from a database table, then it stores the fact that it is Null, as this is different to it never having been given a value and is information your program might want to treat differently.
Also note that a variant storing and empty string "" is not the same as being empty.
ie "" does not equal Empty (and it is not the same as Null either!)
When using MS Access tables to store text, I would advise against setting the "Allow Zero Length" field property to true, which is the default setting, as this means your database field will be able to store "" (ie an empty string) as well as a Null value. Any code you write then has to work with the possibility that the field will store a "" or a Null. It's easier just to work with a Null.
(It's very rare to need to store an empty string in a database table).
Another useful technique is to use MyString = Nz(MyStringDatabaseField)
to convert any nulls to be an empty string. At least then your code only has to test for empty strings and not for Nulls as well. This technique will also simplify code working with access tables that store empty strings. Sometimes it may be appropriate to use `MyInteger=Nz(MyIntegerDatabaseField) to convert any nulls to 0, but I am very uncomfortable with this as 0 has a more meaning than an empty string and really Null <> 0!
Note that SQL statements which use an OUTER JOIN between their tables, can result in the returned recordset containing NULL values in fields where the underlying table field is defined to prevent NULLs being stored.
Note that if you do not use a variant the data types then default values may be used unexpectedly. Eg
Dim aInt As Integer
aInt = Empty
Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"
The code below helped me to understand the difference between the various functions that can be used to inspect variant variables
Sub ExperimentsWithVariants()
Dim aInt As Integer
aInt = Empty
Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"
Dim avar As Variant ' The results shown as comments below were created when aVar was declared as a variant
Debug.Print "-----------------------"
Debug.Print "NOT SET:"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Empty
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
If avar = Empty Then
Debug.Print " "
Debug.Print "avar = Empty so the above would be the same if you set avar = Empty explicitly"
Debug.Print " """
Else
avar = Empty
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO Empty"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Empty
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
End If
avar = Null
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO NULL"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Null
Debug.Print "aVar = Empty ", (avar = Empty) ' Null
Debug.Print "aVar", avar ' Null
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' True
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' False
Debug.Print "aVar = """"", (avar = "") ' Null
Debug.Print "aVar = 0", (avar = 0) ' Null
avar = ""
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO EMPTY STRING ie """""
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) '
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' False
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' String
' Note
' Is empty returns false, whereas ="" returns NULL
avar = 1.23
Debug.Print "-----------------------"
Debug.Print "SET to 1.23:"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Double
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
' You can test for both an IsEmpty AND an empty string (ie "" ) AND a null value with:
' IIf(Len(avar & vbNullString)
Debug.Print "-----------------------"
Debug.Print "Using IIf(Len(avar & vbNullString) "
Debug.Print "-----------------------"
avar = ""
Debug.Print """""=", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty, or Empty String", "NOT")
avar = "1"
Debug.Print "1 = ", IIf(Len(avar & vbNullString) = 0, "Null IsEmpty,or Empty String", "NOT")
avar = Null
Debug.Print "Null = ", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty or Empty String", "NOT")
avar = Empty
Debug.Print "Empty = ", IIf(Len(avar & vbNullString) = 0, "Null or Empty String", "NOT")
Debug.Print "-----------------------"
Debug.Print "using TypeName"
Debug.Print "-----------------------"
Dim dbl1 As Double
Debug.Print "TypeName(dbl1) ", TypeName(dbl1) ' Double
Dim int1 As Integer
Debug.Print "TypeName(int1) ", TypeName(int1) ' Integer
Dim str1 As String
Debug.Print "TypeName(str1) ", TypeName(str1) ' String
End Sub
Sub ExperimentsWithNz()
Debug.Print " "
Debug.Print "---------------------------------------------------------------------- "
Debug.Print "---------------------------------------------------------------------- "
Debug.Print "1a Nz(Null)="""" =", Nz(Null) = ""
Debug.Print "1b IsNull(Nz(Null)) =", IsNull(Nz(Null)) ' False
Debug.Print "---------------------------------------------------------------------- "
Dim aVar As Variant
Debug.Print "2a Nz(aVar) Unassigned =", Nz(aVar) ' Null
aVar = Empty
Debug.Print "2b Nz(aVar) Empty =", Nz(aVar) ' Null
aVar = Null
Debug.Print "2c Nz(aVar) Null =", Nz(aVar) ' Null
Debug.Print "2d IsNull(Nz(aVar)) Null=", (IsNull(Nz(aVar))) ' Null
aVar = ""
Debug.Print "2e Nz(aVar) """" =", Nz(aVar) ' ' ie an empty string
Debug.Print "---------------------------------------------------------------------- "
Dim str1 As String
Debug.Print "3a Nz(str1) Unassigned =", Nz(str1) ' 0
str1 = Empty
Debug.Print "3b Nz(str1) Empty =", Nz(str1) ' 0
Debug.Print "---------------------------------------------------------------------- "
Dim int1 As Integer
Debug.Print "4a Nz(int1) Unassigned =", Nz(int1) ' 0
int1 = Empty
Debug.Print "5b Nz(int1) Empty =", Nz(int1) ' 0
' The following line cannot run as a string cannot be assigned Null
' str1 = Null
End Sub
Sub DealingWithEmptyStringsInADatabaseTable()
Dim aVar As Variant
Debug.Print "UNdeclared: ", Nz(aVar, 1)
aVar = Empty
Debug.Print "aVar=Empty ", Nz(aVar, 1)
aVar = Null
Debug.Print "aVar=Null ", Nz(aVar, 1)
aVar = ""
Debug.Print "aVar="""" ", Nz(aVar, 1)
Debug.Print " -------------------------------------------------------"
Debug.Print "Dealing with empty string in a database table"
aVar = ""
Debug.Print "IIf(aVar = "", 1, 0) ", IIf(aVar = "", 1, 0)
Debug.Print " "
Debug.Print " "
Debug.Print "-------------------------------------------------------"
Debug.Print "Dealing with a table field that can have Null or an Empty string"
Debug.Print "leads to more complex code than if is just stores NULL."
Debug.Print " "
Debug.Print "The code below shows WHY you should set the ""; Allow Zero Length "" property of access tables to false"
Debug.Print " "
aVar = Null
Debug.Print "1 Null : IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)
aVar = ""
Debug.Print "2 Empty String: IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)
Debug.Print " "
Debug.Print "Both lines 1 and 2 above work."
Debug.Print " "
Debug.Print " "
aVar = Null
Debug.Print "3 Null : Nz(aVar, 1) ", Nz(aVar, 1)
aVar = ""
Debug.Print "4 Empty String: Nz(aVar, 1) ", Nz(aVar, 1)
Debug.Print " "
Debug.Print "however, line 4 does not work for empty string."
Debug.Print "3 & 4 are much simpler than 1 & 2, but if your field can store """" and Null"
Debug.Print "you have to use 1 & 2. Whi