excel - What is the different between IsNull, IsEmpty, =Empty, and an empty string ie "" and why might I use variants

Question

I'm trying to understand what a Null is and also what an Empty variable is. Are they the same? How do empty strings fit in?

When creating MS Access tables why do fields have an option of "Allow Zero Length String"?

I've been struggling with loading data from an Access database into variables (eg using DAO or ADO) and I find myself having to declare all the variables I use a Variant. This seems so wrong to me.

Does anyone have any good example code that demonstrates how these differ and can you explain why I might use a variant.

Can anyone advise.

(I have posted my own answer with lots of simple example code that has helped me. It shows how lots of functions work with variants, I hope people with similar difficulties find this useful)

Answer 1

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