Better explanation of NULL, EMPTY, and NOTHING (SOLVED)

[phantom1975]

I've read the help files and can't really digest what I'm reading. What's the difference between these?

[Aladin Akyurek]

I've read the help files and can't really digest what I'm reading. What's the difference between these?

Have a clean worksheet.

Leave A1 empty.

In A2 enter:

=IF(B1,1,"")

Stay away from column B.

In C1 enter & copy down:

=ISBLANK(A1)

You'll get:

TRUE
FALSE

In D1 enter & copy down:

=COUNTBLANK(A1)

You'll get:

1
1

In E1 enter & copy down:

=A1=""

You'll get:

TRUE
TRUE

In F1 enter & copy down:

=LEN(A1)

You'll get:

0
0

In G1 enter & copy down:

You'll get:

0
1

A formula generated "" is often called a blank, nothing, null (string). ISBLANK is the only function (predicate) along with COUNTA that distinguishes between an empty cell and a cell that houses "" as formula result. Note the inconsistency between the BLANK bits in ISBLANK and COUNTBLANK.

I try to speak about empty cells and cells housing formula-generated blanks.

In practice, blank and empty is used to characterize a cell that contains nothing, even if the cell houses "" as formula result.

[Richie(UK)]

... and this is my understanding from the VBA perspective:

Sub WhatsTheDifference()
Dim varMyValue As Variant, bNullCheck As Boolean, msg As String
Dim bEmptyCheck As Boolean

'** Null
'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.

'** Empty
'Indicates that no beginning value has been assigned to a Variant variable. An Empty
'variable is represented as 0 in a numeric context or a zero-length string ("") in a
'string context.

'part one - no value yet assigned to varMyValue, ie it is empty (uninitialised)
bNullCheck = IsNull(varMyValue)
bEmptyCheck = IsEmpty(varMyValue)
msg = msg & "Variable data := " & varMyValue & vbCrLf & _
    "Null status := " & bNullCheck & vbCrLf _
    & "Empty status := " & bEmptyCheck
MsgBox prompt:=msg, Title:="Test status"

'part two - value assigned to varMyValue, so no longer empty
msg = ""
varMyValue = 123
bNullCheck = IsNull(varMyValue)
bEmptyCheck = IsEmpty(varMyValue)
msg = msg & "Variable data := " & varMyValue & vbCrLf & _
    "Null status := " & bNullCheck & vbCrLf _
    & "Empty status := " & bEmptyCheck
MsgBox prompt:=msg, Title:="Test status"

'part three - null status assigned to varMyValue, no longer empty but no valid data
msg = ""
varMyValue = Null
bNullCheck = IsNull(varMyValue)
bEmptyCheck = IsEmpty(varMyValue)
msg = msg & "Variable data := " & varMyValue & vbCrLf & _
    "Null status := " & bNullCheck & vbCrLf _
    & "Empty status := " & bEmptyCheck
MsgBox prompt:=msg, Title:="Test status"

'part four - empty status assigned to varMyValue
'note that this is not the same, in this case, as using "" (try it for yourself!)
msg = ""
varMyValue = Empty
bNullCheck = IsNull(varMyValue)
bEmptyCheck = IsEmpty(varMyValue)
msg = msg & "Variable data := " & varMyValue & vbCrLf & _
    "Null status := " & bNullCheck & vbCrLf _
    & "Empty status := " & bEmptyCheck
MsgBox prompt:=msg, Title:="Test status"

End Sub

I haven't included Nothing in the above example as this is really something entirely different. It is used to 'release' an object variable from memory in the following manner Set oMyObjectVar = Nothing.

HTH

[Yogi Anand]

Hi phantom1975:

In addition to Aladin's and Richie's contribution ...

"" .......... would be an EMPTY string

NULL ...... for no valid data

NOTHING in VBA is used to identify non-existence of an object

just some thoughts to share with you!


Edit ... indicated NULL to be for no valid data

[phantom1975]

I love the way you all make these things so digestable! Thanks a bunch! :P