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]

Re: Better explanation of NULL, EMPTY, and NOTHING

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

 

[donfaison]

late to this thread, but how to identify (in vba) a cell that has "" as opposed to 123 or abc?

 

[Scott T]

late to this thread, but how to identify (in vba) a cell that has "" as opposed to 123 or abc?

This code would show the message box if S7 did not have any thing in it or if the formula returned that returns "". If it has 123 or abc it would not show the message box since the condition is false.

Sub mblank()
If Range("S7") = "" Then
    MsgBox ("S7 is blank")
End If

End Sub