Blank, Null, Empty?

M

maxhugen

I have ongoing problems differentiating whether a cell IsBlank or not!

Q1: Is a blank cell the same as an empty string? I tried deleting a
value in cell C4, then in another cell entered =C4="" and another cell:
= IsBlank(C4). They both returned TRUE, so I assume they are the same
(blank and empty string).

Q2: I sent someone a spreadsheet and they deleted the contents of a
cell (well, I assume they did, there's no value in it).

BUT, when I check for an empty string, or isblank, they both show
false! I then tried comparing to zero just in case: =C4=0. False
again.

I then tried using the immediate window in vba:
?worksheet.cells(4,3)

But it returned nothing.

So, what the heck is the value of cell C4?

It's not zero, or an empty string, or blank... but no value can be
seen, nor is a value returned in code!

I'm pretty Ok in vba, but type-casting sure gives ne a few headaches in
Excel!

Any suggestions would really be appreciated.

MTIA
 
E

Earl Kiosterud

Max,

Q1. Both your formula refer to an empty cell (you've used the delete key).
But ISBLANK isn't always the same as an empty string. With ="" in C4, the
ISBLANK would return FALSE (the cell isn't blank) but =C4="" returns TRUE
because "" is an empty string.

Excel doesn't really have a way of assigning nothing to a cell from a
formula, like =NULL. We wish it did.

Q2. On what do you base your conclusion that the cell contains nothing? Do
you see nothing in the formula bar when the cell is selected? Try
?Range("C4").Formula. Check Format - Cells - Protection - Hidden. That
will hide a formula in the cell if the worksheet is protected (you won't see
the formula in the formula bar, and ?Range("C4").Formula will return an
error). What does =LEN(cell) yield. Check the font color of the cell
(might be same as cell fill color).
 
M

maxhugen

Hi Earl

Len(cell) revealed there was 1 character in there - asc 32 (a space!)

Looks like the user "'cleared" contents of cells by using the space ba
instead of delete!

It's a bit difficult to make Excel "bullet-proof" for users, isn't it?

Thanks very much!

Have a great day, Ma
 
Top