Difference Between Isempty And Isnull

[venkat1926]

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

Dim c As range 
Set c=range("d2") 
msgbox isempty(c) 
msgbox isnull(c) 

thanks

[jindon]

try

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 

[jindon]

What happened huh?

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

I just can't believe this...

[venkat1926]

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

[venkat1926]

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.

[jindon]

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.

[Reafidy]

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

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 

[venkat1926]

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

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

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

[jindon]

What happen if you write like

c[B].Value[/B] = Null