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

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
I've read the help files and can't really digest what I'm reading. What's the difference between these?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Re: Better explanation of NULL, EMPTY, and NOTHING

phantom1975 said:
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.
 
Upvote 0
... and this is my understanding from the VBA perspective:
Code:
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
 
Upvote 0
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
 
Upvote 0
late to this thread, but how to identify (in vba) a cell that has "" as opposed to 123 or abc?
 
Upvote 0
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.
Code:
Sub mblank()
If Range("S7") = "" Then
    MsgBox ("S7 is blank")
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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