Empty vs Null vs ""

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
Is the null (0 length) string ("") the same as Empty or not? The Immediate window seems confused or ambivalent:

Code:
?"" = empty
True

?isempty("")
False

xblank = ""
?xblank = empty
True

?isempty(xblank)
False

It appears that Null is different than anything else:
Code:
?null=empty
Null

?null=""
Null

?null=null
Null

?isnull(null)
True

xnull = null
?xnull = null
Null

?isnull(xnull)
True

I am interested in this because I want to choose a value to return from a Variant subfunction that cannot be confused with any other value. It looks like Null is that value.

Code:
Private function Test(. . .)
 . . .

'Do some work. If the result fails some test, let the caller know by returning a Null result
If (some test) then Test = Null: Exit Function

 . . .

End Function

Comments?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
From the Excel VBA help:

"The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string."
 
Upvote 0
From the Excel VBA help:

"The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized.
I assume you mean a Variant variable, right? I think that when declared, and before initialized, numeric variables have a value of zero, string variables a value of "", and Booleans a value of False. Correct?

It is also not the same as a zero-length string (""), which is sometimes referred to as a null string."
OK

But why do these comparisons return True, but the IsEmpty() function returns False?
Code:
?"" = empty
True

?isempty("")
False

xblank = ""
?xblank = empty
True

?isempty(xblank)
False

Anyway, I have this subfunction that does some processing and needs to return one of three possible values:

  1. A number (if the processing was successful)
  2. A Value error (if something is wrong with the data)
  3. Some other value that cannot ever be confused with either of the other two to indicate that this value should be skipped. The function is called repeatedly to handle data in a range. Some of that data will be skipped, but not because of an error. Errors will terminate processing. Skipped data will continue to process the rest of the range.

I see these options for #3 :

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]Value Returned
[/TD]
[TD="align: center"]Expression[/TD]
[/TR]
[TR]
[TD="align: center"]Null[/TD]
[TD]Result=Null[/TD]
[/TR]
[TR]
[TD="align: center"]Empty[/TD]
[TD]Result=Empty[/TD]
[/TR]
[TR]
[TD="align: center"]Null string[/TD]
[TD]Result=""[/TD]
[/TR]
[TR]
[TD="align: center"]Other string[/TD]
[TD]Result="Skip"[/TD]
[/TR]
</tbody>[/TABLE]

Is there any reason to prefer one over the others?

Thanks
 
Upvote 0
... I think that when declared, and before initialized, numeric variables have a value of zero, string variables a value of "", and Booleans a value of False. Correct?..
No, when a Variant variable is declared, and before initialized, it is set to Empty. "An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context. IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty." That's why "" = Empty evaluates to True, but IsEmpty("") returns False.

On Null: "Null is a value indicating that a variable contains no valid data. Null is the result of an explicit assignment of Null to a variable or any operation between expressions that contain Null." That's why Null = Empty evaluates to Null and not False.

So, it looks like you can set your function to return either Empty or Null and then unambiguously test the result with, respectively, IsEmpty or IsNull.
 
Upvote 0
No, when a Variant variable is declared, and before initialized, it is set to Empty.
I think we are talking past each other. I agree with you re Variants. My point was that variables that are declared as numbers (not Variant) are initialized as zero, those declared as strings are initialized as "", and Booleans as False. I just tested it again. ;)

"An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context. IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty." That's why "" = Empty evaluates to True, but IsEmpty("") returns False.

On Null: "Null is a value indicating that a variable contains no valid data. Null is the result of an explicit assignment of Null to a variable or any operation between expressions that contain Null." That's why Null = Empty evaluates to Null and not False.

So, it looks like you can set your function to return either Empty or Null and then unambiguously test the result with, respectively, IsEmpty or IsNull.
OK, thanks
 
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top