Results 1 to 5 of 5

Better explanation of NULL, EMPTY, and NOTHING (SOLVED)

This is a discussion on Better explanation of NULL, EMPTY, and NOTHING (SOLVED) within the Excel Questions forums, part of the Question Forums category; I've read the help files and can't really digest what I'm reading. What's the difference between these?...

  1. #1
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default Better explanation of NULL, EMPTY, and NOTHING (SOLVED)

    I've read the help files and can't really digest what I'm reading. What's the difference between these?
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,410

    Default Re: Better explanation of NULL, EMPTY, and NOTHING

    Quote Originally Posted by phantom1975
    I've read the help files and can't really digest what I'm reading. What's the difference between these?
    Have a clean worksheet.

    Leave A1 empty.

    In A2 enter:

    =IF(B1,1,"")

    Stay away from column B.

    In C1 enter & copy down:

    =ISBLANK(A1)

    You'll get:

    TRUE
    FALSE

    In D1 enter & copy down:

    =COUNTBLANK(A1)

    You'll get:

    1
    1

    In E1 enter & copy down:

    =A1=""

    You'll get:

    TRUE
    TRUE

    In F1 enter & copy down:

    =LEN(A1)

    You'll get:

    0
    0

    In G1 enter & copy down:

    You'll get:

    0
    1

    A formula generated "" is often called a blank, nothing, null (string). ISBLANK is the only function (predicate) along with COUNTA that distinguishes between an empty cell and a cell that houses "" as formula result. Note the inconsistency between the BLANK bits in ISBLANK and COUNTBLANK.

    I try to speak about empty cells and cells housing formula-generated blanks.

    In practice, blank and empty is used to characterize a cell that contains nothing, even if the cell houses "" as formula result.

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,330

    Default

    ... and this is my understanding from the VBA perspective:
    Code:
    Sub WhatsTheDifference()
    Dim varMyValue As Variant, bNullCheck As Boolean, msg As String
    Dim bEmptyCheck As Boolean
    
    '** Null
    'A value indicating that a variable contains no valid data. Null is the result of an
    'explicit assignment of Null to a variable or any operation between expressions that
    'contain Null.
    
    '** Empty
    'Indicates that no beginning value has been assigned to a Variant variable. An Empty
    'variable is represented as 0 in a numeric context or a zero-length string ("") in a
    'string context.
    
    'part one - no value yet assigned to varMyValue, ie it is empty (uninitialised)
    bNullCheck = IsNull(varMyValue)
    bEmptyCheck = IsEmpty(varMyValue)
    msg = msg & "Variable data := " & varMyValue & vbCrLf & _
        "Null status := " & bNullCheck & vbCrLf _
        & "Empty status := " & bEmptyCheck
    MsgBox prompt:=msg, Title:="Test status"
    
    'part two - value assigned to varMyValue, so no longer empty
    msg = ""
    varMyValue = 123
    bNullCheck = IsNull(varMyValue)
    bEmptyCheck = IsEmpty(varMyValue)
    msg = msg & "Variable data := " & varMyValue & vbCrLf & _
        "Null status := " & bNullCheck & vbCrLf _
        & "Empty status := " & bEmptyCheck
    MsgBox prompt:=msg, Title:="Test status"
    
    'part three - null status assigned to varMyValue, no longer empty but no valid data
    msg = ""
    varMyValue = Null
    bNullCheck = IsNull(varMyValue)
    bEmptyCheck = IsEmpty(varMyValue)
    msg = msg & "Variable data := " & varMyValue & vbCrLf & _
        "Null status := " & bNullCheck & vbCrLf _
        & "Empty status := " & bEmptyCheck
    MsgBox prompt:=msg, Title:="Test status"
    
    'part four - empty status assigned to varMyValue
    'note that this is not the same, in this case, as using "" (try it for yourself!)
    msg = ""
    varMyValue = Empty
    bNullCheck = IsNull(varMyValue)
    bEmptyCheck = IsEmpty(varMyValue)
    msg = msg & "Variable data := " & varMyValue & vbCrLf & _
        "Null status := " & bNullCheck & vbCrLf _
        & "Empty status := " & bEmptyCheck
    MsgBox prompt:=msg, Title:="Test status"
    
    End Sub
    I haven't included Nothing in the above example as this is really something entirely different. It is used to 'release' an object variable from memory in the following manner Set oMyObjectVar = Nothing.

    HTH
    Richie

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi phantom1975:

    In addition to Aladin's and Richie's contribution ...

    "" .......... would be an EMPTY string

    NULL ...... for no valid data

    NOTHING in VBA is used to identify non-existence of an object

    just some thoughts to share with you!


    Edit ... indicated NULL to be for no valid data
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    I love the way you all make these things so digestable! Thanks a bunch! :P
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com