Empty vs Null vs ""

[JenniferMurphy]

Is the null (0 length) string ("") the same as Empty or not? The Immediate window seems confused or ambivalent:

?"" = empty
True

?isempty("")
False

xblank = ""
?xblank = empty
True

?isempty(xblank)
False

It appears that Null is different than anything else:

?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.

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?

 

[Tetra201]

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."

 

[JenniferMurphy]

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?

?"" = 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 :

Value Returned	Expression
Null	Result=Null
Empty	Result=Empty
Null string	Result=""
Other string	Result="Skip"

<tbody>
</tbody>

Is there any reason to prefer one over the others?

Thanks

 

[Tetra201]

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

 

[JenniferMurphy]

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