Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Difference Between Isempty And Isnull

1. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## 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. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## 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. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## 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. ## Re: Difference Between Isempty And Isnull

I beleive Jindons example explains everything but try it with this tiny change:

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. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Difference Between Isempty And Isnull

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

```

Excel Video Tutorials / Excel Dashboards Reports

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

#### 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