Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Difference Between Isempty And Isnull

  1. #1
    Join Date
    13th May 2006
    Location
    India
    Posts
    942

    Difference Between Isempty And Isnull

    what is the logical difference betwee ISEMPTY and ISNULL. explanations with a few examples will be useful. For example if a cell is blank because of a formula is it isempty or isnull. on the other hand if no entry is made in a cell, is it isempty. of course these functions refer to variables. assume I convert the cells to variable for e.g
    VB:
    Dim c As range 
    Set c=range("d2") 
    msgbox isempty(c) 
    msgbox isnull(c) 
    
    
    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Difference Between Isempty And Isnull

    try
    VB:
    Dim myNull 
    Sub test() 
        myCheck myNull, "Not sepcified" 
        myNull = Null 
        myCheck myNull, "Null" 
        myNull = Empty 
        myCheck myNull, "Empty" 
    End Sub 
     
    Sub myCheck(myNull, myTitle) 
        MsgBox "IsNull(myNull) = " & IsNull(myNull) & _ 
        "IsEmpty(myNull) = " & IsEmpty(myNull), ,"Null = " & myTitle 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Difference Between Isempty And Isnull

    What happened huh?

    Are you always like this? Answering to other leaving your own question unsolved?

    I just can't believe this...
    Last edited by jindon; April 28th, 2007 at 15:17.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th May 2006
    Location
    India
    Posts
    942

    Re: Difference Between Isempty And Isnull

    I accept the rebuke. extemely sorry. I was in and out of the hospital. so my reactions are not very good. Your posting must have come when I was just going into the hospital and mulst have missed it later. anyhow nothing can be the excuse.

    thank you for yur clarificiation. still I have some more doubts. I shall post these when I am mentally and phsically come back to normal.

    thank you once again

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th May 2006
    Location
    India
    Posts
    942

    Re: Difference Between Isempty And Isnull

    I apologise for continuing this thread after some days due to some personal reasons.


    though my knowledge on vba is average sometimes I go back to fundamentals to understand the grammar clearly. I have no clear idea of the use of ISEMPTY and ISNULL in vba.I went thoght the help files on these. I also searched the various newsgsroups still my mind is not clear.
    I have given some examples (though trivial) to explain my doubts

    A1 has entry 2
    B1 has entry, a formula
    =if(a1<3,"",A1)
    C1 has no entry at all . to be certain c1 is highlighted and clicked edit-clear-all.
    d1 has entry 0
    ====================
    now in the immediate window of VBE the following are the results.

    ?isempty([b1])
    False (because it has formula)
    ?isnull([b1])
    False(??)--why?
    ?[b1]=""
    True
    ?[b1]=0
    False(this is understood)

    ?isempty([c1])
    True(this is ok)
    ?isnull([c1])
    False
    (why ? C1 contains no valid data. why is it false. should it not be true) quote from help in vbe for Isnull--
    Returns a Boolean value that indicates whether an expression contains no valid data (Null).
    unquote

    ?isempty([d1])
    False(understood as it contains an entry 0)
    ?isnull([d1])
    False(it contains valid data 0)
    ?[d1]=0
    True(OK)

    please clear the cobwebs in my mind and help me to u nderstand the logic better. I know that essentially the properties are for initializing varibale. But I think these an also be used for worksheet cells or ranges.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Difference Between Isempty And Isnull

    Haven't you tried my code?

    IsNull function is to test if the variable has "Null value" or not.

    Null value is a unique value that can be stored in a variant type variable and it is not the value like Empty, variable before initialization, or Null string, 0 length string.

    I think the most general way you use "Null" value in reality is to assing it intentionally like I showed you in the code.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Difference Between Isempty And Isnull

    I beleive Jindons example explains everything but try it with this tiny change:
    (it made it a lot easier to read for me)

    VB:
    Dim myNull 
    Sub test() 
        myCheck myNull, "Not specified" 
        myNull = Null 
        myCheck myNull, "Null" 
        myNull = Empty 
        myCheck myNull, "Empty" 
    End Sub 
     
    Sub myCheck(myNull, myTitle) 
        MsgBox "IsNull(myNull) = " & IsNull(myNull) & vbLf & vbLf & _ 
        "IsEmpty(myNull) = " & IsEmpty(myNull), , "myNull = " & myTitle 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    13th May 2006
    Location
    India
    Posts
    942

    Re: Difference Between Isempty And Isnull

    I seem to somewhat understand. thanks both of you in trying to clear the cobwebs in the mind. As Jindon says "null" has to be inentionally assigned to the variable but not what the variable has alrady. I prepared a trivial sub below
    VB:
    Sub test() 
        Dim c 
        c = Null 
        MsgBox IsNull(c) 
    End Sub 
    
    
    the msgbox gives "true".

    ok. one more question. can I set variable c to a range. for e.g.
    set c=range("a1")
    then what happens.
    here is anohter code
    VB:
    Sub test() 
        Dim c 
        Set c = Range("a1") 
        c = Null 
        MsgBox IsNull(c) 
        MsgBox IsNull(Range("a1")) 
    End Sub 
    
    
    the first msgbox gives "true" but the second box gives "false"

    thanks again
    venkat

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Difference Between Isempty And Isnull

    What happen if you write like
    VB:
    c[B].Value[/B] = Null 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Vba Error 3704 With Isnull
    By chucktx in forum Excel and/or SQL Help
    Replies: 7
    Last Post: May 18th, 2007, 03:51
  2. textbox validation using IsEmpty
    By laot7210027YA in forum EXCEL HELP
    Replies: 1
    Last Post: July 7th, 2006, 12:53
  3. IsEmpty returns TRUE even when FALSE
    By teamkoso in forum EXCEL HELP
    Replies: 7
    Last Post: January 6th, 2006, 03:25
  4. NotNull / IsNull
    By Khemikal in forum EXCEL HELP
    Replies: 1
    Last Post: November 8th, 2004, 22:29
  5. Do.....Loop until IsEmpty(....)
    By h in forum EXCEL HELP
    Replies: 2
    Last Post: June 8th, 2004, 03:29

Bookmarks

Posting Permissions

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