Is the cell really blank?

[Otto Moehrbach]

Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank cell
(True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly blank?
Thanks for your help. Otto

 

[Dave Peterson]

isempty has always worked for me when checking a range.

I don't recall seeing that warning, but there are lots of posts to the
newsgroups.

 

[Jim Rech]

I realize that this check is good only for a blank cell, not for a cell
I have to disagree, Otto. When I run this code:

If ActiveCell.Value = "" Then Beep

when a cell that has this formula in it is selected:

=IF(TRUE,"","")

I get a beep.

 

[Gary''s Student]

It appears that IsEmpty() can distinguish blank from empty:

Sub Macro1()
MsgBox (IsEmpty(Cells(1, 1).Value))
End Sub

will anounce false if A1 contains:

=IF(1=1,"","")

 

[Dave Peterson]

And after reading Jim Rech's response, I want to clarify mine.

isempty(range("a1")) will return true if there's no formula and no constant in
that cell.

isempty(range("a1")) will return false if there's a formula in that cell--no
matter what it returns.

And to make matters slightly worse, if you convert a formula that evaluated to
"" to a value, then the formula isempty(range("a1")) will return false.

But if range("a1").value = "" won't care if the cell is empty or evaluates to ""
(or used to be a formula that evaluate to "" and was converted to values).
It'll return True.

 

[Otto Moehrbach]

Jim
I didn't word my statement correctly. I meant just what you said,
namely that the If ....."" Then is True if the cell is blank, regardless of
why it is blank. Otto

 

[Otto Moehrbach]

Thanks Dave, Jim, and the Student. That clears it up for me. Otto

 

[Dick Kusleika]

Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/archives/2004/08/12/testing-for-empty-cells/

I don't think it necessarily adds anything to what was said here, but it
may.

 

[Otto Moehrbach]

Dick
That's a good write-up. Thanks for that.
I have one question I want to ask to keep me out of trouble with this in
the future. You say, as have others, that IsEmpty, for the purposes
discussed in this thread, should act on the Value property of the range
object. And that works. But I have always used it (in ignorance???) on the
range object itself, and not on the Value property of the range object. Am
I liable to step in something by doing this? Thanks again. Otto

 

[Dave Peterson]

I've been one of the offenders who used
if isempty(somerange) then

But after reading Dick's blog, I'm gonna (try to) change to:
if isempty(somerange.value) then

From an English point, it always made more sense to me to talk about the
container (the cell) than the value. But looking at Dick's screen print, I see
the error of my ways.

But as a saving grace, .value is the default property of a range.

 

[Dick Kusleika]

Otto: I agree with everything Dave said. When you leave off the .Value
property, you are still testing the .Value property because it's the default
of the Range object. There are two potential pitfalls to omitting Value:
MS could change the default property or Range; You force the compiler to
look up the default property which is a performance hit.

The performance hit, I'll bet, is so small that you couldn't measure it in a
10,000 line program and the chance that MS will change the default property
is even less. So I think you're safe leaving it off. I tried to get into
the habit of always specifiying the default property a couple of years ago,
which is why I use it.

 

[Otto Moehrbach]

Dave & Dick
I've resolved to always state the .Value also. Thanks for your help.
Otto