NULL vs EMPTY vs 0-length string

**vba_php** · 08-30-2020, 08:44 PM

I've got a question for the real experts here. Can any one of you help me understand the difference between all of these, when checking a cell for no content?

Please Login or Register  to view this content.

I've been using this program for a long long time, and everytime I have to check something like this I get very annoyed because I've always run into anomalies with regard to these statements. I am currently putting together a knowledgebase for a student and one of my routines has to check a cell for no content. And to cover all the bases of Excel ""quirks"", this is the code I'm using:

Please Login or Register  to view this content.

Can someone tell me why I'm not trusting Excel to be reliable? I've run into this in the past. Is it just because I'm not a professional developer and don't know every little detail about this program? In this example, rstart has been set in the code, and declared as a range object.

thanks.

**6StringJazzer** · 08-30-2020, 09:06 PM

isnull()
Do not use IsNull on a range. Use it on a Variant variable. It returns TRUE iff the variable contains no valid data.

= ""
For a cell, returns TRUE iff the cell has no content, or the content is the null string. This will be TRUE for an empty cell and also true if there is a formula that returns "" such as
=IFERROR(VLOOKUP(A1,B2:F2,5,TRUE),"")

isempty()
On a cell it returns TRUE iff the cell has no content, that is, no value and no formula. On a Variant variable it returns TRUE iff the variable has not been initialized.

= null
Do not use Null to check the value of a cell. Use it on a Variant variable. This

Please Login or Register  to view this content.

is equivalent to

Please Login or Register  to view this content.

**vba_php** · 08-30-2020, 09:42 PM

what a very useful post. thanks so much jazzer! I assume you play guitar.

NULL vs EMPTY vs 0-length string

LinkBack

Thread Tools

Rate This Thread

Display

NULL vs EMPTY vs 0-length string

Re: NULL vs EMPTY vs 0-length string

Re: NULL vs EMPTY vs 0-length string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to return empty string instead of null when column table is empty

remove/replace a variable string length based on length and wildcard

[SOLVED] Replace empty string with null

[SOLVED] Shorten string length if exceeds 31 characters (max sheet name length)

Length of the longest consecutive data series with values not null

Find a cell that has a null value, and replace with an empty string.

If null be empty

Bookmarks

Bookmarks

Posting Permissions