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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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

  1. #1
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,462
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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! *****************

  6. #6
    New Member
    Join Date
    Jan 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    late to this thread, but how to identify (in vba) a cell that has "" as opposed to 123 or abc?

  7. #7
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,664
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

      
    Quote Originally Posted by donfaison View Post
    late to this thread, but how to identify (in vba) a cell that has "" as opposed to 123 or abc?

    This code would show the message box if S7 did not have any thing in it or if the formula returned that returns "". If it has 123 or abc it would not show the message box since the condition is false.
    Code:
    Sub mblank()
    If Range("S7") = "" Then
        MsgBox ("S7 is blank")
    End If
    
    End Sub
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

User Tag List

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