Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Understanding Null, Empty, Blank, #N/A, ZLS, Nothing, Missing

Published:
Updated:

If you are on this page looking only for ways to handle Null values or Missing parameters, you can scroll down to the appropriate section, and skip the rest. Each section should be intelligible when read out of sequence.

Introduction

I have been programming with VBA for Access and Excel for quite some time, and I remember that even after several years of experience, I still had trouble with the different ways to represent the absence of something. The Tao teaches us that the usefulness of a jug resides in the void inside. Let's see how absence can be tamed and made useful.

This article will hopefully save some time for VB developers, even relatively experienced ones, by defining the concepts in context, and perhaps give fellow Experts some ideas on how to explain them to beginners.

The section about Null is larger than the others, due to a personal bias towards databases, but also because, among the keywords for absence, Null raises the most questions on Experts-Exchange.

[I must confess: not all puns in the article were intended, not quite...]



The origins of nothingness

Historians tell us that the number zero was considered diabolical during the transition from Roman numerals to Arabic digits. In a way, it still is. Saying "I have no money" is logical, but saying "I have zero money" is awkward; the negative is transferred to the word "zero". It's also tricky -- you can divide by any number but zero, and powerful -- any number multiplied by zero equals zero.

By the time calculators became available, zero was no longer diabolical. When I turned on my first calculator, it greeted me with "[    0.]", to no surprise. Zero is the standard uninitialized state of a number stored digitally. In many programming languages today, all numeric variables have an initial value of zero.

Computers also deal with text, represented as "strings of characters". A fixed-length String variable is initialized (for example with spaces), and variable-length strings are initially empty: "". Their length is thus zero, so an empty string is also called a "zero-length string", or ZLS.

One computer language, BASIC, tried to be more friendly than most others, in that it did not require variables to be created before using them. This leads to a peculiar behaviour. Here is a typical silly program:
10: LET A = 2
                      20: LET B = A + A
                      30: PRINT "A = " + A; " B = " + B
                      ----------
                      A = 2 B = 4

Open in new window

The LET statements silently create the variables A and B. From the expression, it's apparent that these are numbers. However, when we comment out the first line, we get:
10: REM (A does not exist)
                      20: LET B = A + A
                      30: PRINT "A = " & A; " B = " & B
                      ----------
                      A =  B = 0

Open in new window

In line 20, A is also created silently, and obviously treated as zero. But in line 30, it isn't, or we would see "A = 0" in the output. So, depending on the context, A is either zero, or a zero-length string. The variable A is unidentified, and the BASIC interpreter tries to make the best of it. Today, we say that A is "Empty".

The same problem occurs with spreadsheet applications. Initially, all cells are empty. In Excel, they are called "Blank" until the user types something. When a user writes an expression involving a blank cell, it makes sense to treat the cell as 0 or "", exactly like uninitialized variables in BASIC. As such, blank cells viewed from VB are also Empty.

This is fine in user-oriented environments like Excel and VBA. For programmer-oriented tools, this is absurd. If A is undefined, then B = A + A is also undefined.

In SQL, the formally more correct solution was adopted. An empty field, if it doesn't have a default value or if it's cleared by the user, is "Null". Null, another word meaning zero, doesn't behave like Empty. Quite the opposite: every expression involving Null becomes Null (with a few exceptions). Naturally, VBA implements Null as well. We can mimic the BASIC example above in the Immediate Pane of the VBA Editor:
A = Null   ' try also with Empty, 2, or "3"
                      B = A + A
                      C = "result: " + Str(B)
                      PRINT A; B; C

Open in new window

Note: To launch the VBA Editor, use the hotkey Alt + F11 while Excel is running. The immediate pane, if not already visible, is opened by Ctrl + G.

A, B, and C are all Null. B should be (it can't be computed if A is unknown), but the fact that C becomes Null as well might be undesirable. Naturally, there are several solutions to circumvent the problem.

By now, VBA knows about zero, ZLS, Empty, and Null. Excel's "Blank" is not implemented as such, being synonymous with Empty. The last two keywords discussed in this article, Nothing and Missing, are a bit more technical.

BASIC didn't implement objects, but Visual Basic for Applications was specifically designed to work with the exposed objects of the host application. One could say that VBA isn't "object oriented", but "oriented towards object-oriented environments". In any case, one can now declare:
DIM MyObject As Object

Open in new window

In other languages, this creates a pointer to the (default) memory address 0. So it's just another zero. But VB tries to shield the user from such complexities, and instead creates a special value called "Nothing". As long as it's not initialized, MyObject points to Nothing.

Finally, function arguments can be made optional.
Function MyFunction(Arg1, Optional Arg2)

Open in new window

The first argument is required, but not the second. When the function is called with a single argument, what should the second argument's value be? Empty seems a good choice, but for various reasons, it's better to treat that case as a type of error, which the function will have to manage. The argument will be "Missing".


Cheat Sheet

In a nutshell, all the keywords in the title are values of things when they have not been initialized:
    Thing               Value
                          ---------------     -------------
                          Numeric variable    0
                          String variable     "" (ZLS)
                          Variant variable    Empty
                          Excel cell          Blank (~Empty)
                          Access field        Null
                          Object variable     Nothing
                          Optional argument   Missing

Open in new window




About ZLS

ZLS means "zero-length string", and is often simply noted "". There are several methods to determine whether a string variable (a field, a cell) contains a ZLS:

    If A = "" Then
    If A = vbNullString Then
    If A < "A" Then
    If Len(A) = 0 Then

In most code samples, you will see the first (the most readable) or the last (the fastest). It seems logical that the main attribute of a ZLS - its length of 0 - is the easiest to test for.

For practical purposes, vbNullString is equivalent to "", but for purists, there is a subtle and sometimes important difference. Using "" means that a string constant is created, with no characters. Using vbNullString doesn't create a new string and really means "there is no string". The concept of "no string" isn't exposed in VB, so the distinction is only really meaningful when calling an external DLL. In VB, however, using vbNullString is still faster than using "".

The test A < "A" is here only for demonstration, and should not be used. It's meant to illustrate that ZLS is the first string in alphabetical order

Compared to Empty and Blank

Empty will automatically morph into a ZLS when the context is right. So the tests above also evaluate to true if the variable is Empty. If it's important to distinguish Empty from ZLS, the VB function IsEmpty() must be used. The same applies to Excel's cells which can contain a ZLS (for example, by evaluating =""), and ISBLANK() can be used to distinguish blank cells from cells with a ZLS.


Compared to Null

Null, on the other hand will never be confused with ZLS. All the comparison expressions above will evaluate to Null, and 'If' will fail, implicitly treating Null as False.

So the reverse problem may occur: how to test for either Null or ZLS?

    If Nz(A) = "" Then   ' only in Access VBA
    If IsNull(A) Or A = "" Then
    If A & "" = "" Then

The reason why these work is explained in the Null section. In Access VBA, the "fast" expression using Len() is often combined with Nz(), producing

    If Len(Nz(A)) = 0 Then
    If Nz(Len(A)) = 0 Then   ' less common equivalent

In Access tables, text fields are often permitted to store both Null and ZLS, which is almost always a design flaw - see a discussion about it at the end of the Null section.



About Empty

When a variable isn't declared at all, or if it's declared as Variant, it will initially be "Empty". The keyword 'Empty' itself is a relatively recent addition to VB, but the test for emptiness is as old as the Variant variable type.

    If IsEmpty(A) Then

Due to the polymorphic nature of Empty, no other test can reliably determine emptiness. When used in an expression, Empty will always attempt to fit in, and behave like 0 or "". The following expressions all evaluate to True:

    ? Empty = ""
    ? Empty = 0
    ? Not Empty
    ? IsEmpty(Empty)   ' obviously!

When VBA was added to Excel, it became apparent that VB's Variants and Excel's cell values were similar objects, in particular blank cells behave like Empty variables.


Compared to ZLS and Blank

In expressions, Empty will often morph into a ZLS, and is thus functionally equivalent to a string in many cases. In VB, Empty is neither > or < than ZLS, and this is probably the only difference between Empty and blank cells.


Compared to Null

Confusing Empty and Null is rare. However, database developers might get into the habit of thinking "uninitialized means Null", and forget to set Variant variables to Null explicitly when needed. Since Empty and Null do not behave the same way, this might be important.
    Dim varItem As Variant
                          Dim varList As Variant
                          
                          varList = Null   ' Important!
                          For Each varItem In <some list>
                              varList = varList + ", " & varItem.Value
                          Next varItem

Open in new window

The variable varList won't have a leading comma, and will be Null if the list was after all empty.

Another anecdotal connection is that Nz() seems to return Empty when the second argument is left blank:

    Total = Amount + Nz(Fee)   ' Fee can be Null
    If Nz(Field1) = "" Then   ' test for both Null and ZLS

On the other hand, IIf() called with only two arguments -- this is valid in Jet, but not in VB -- will return Null if the expression fails.



About BLANK

The keyword 'Blank' doesn't exist, but one can speak of "blank cells" in Excel, which is consistent with the function to test for them: ISBLANK(). When Excel evaluates an expression with a blank cell, its value is determined by the context.

In a new sheet, the following expressions all return TRUE:

    = ISBLANK(A1)
    = (A1 = "")
    = (A1 = 0)

Apparently, A1 is blank, but at the same time it's equal to "" and 0! It isn't of course, it's just BLANK. There are also contexts where Blank is not 0:

    = COUNT(A1)
    = AVERAGE(A1)

Count will detect that the cell doesn't contain a value, and return 0. Average will return the #DIV/0! error condition, because there is no value (because the count is 0).

Similarly, there is also a context where Blank is not "". The custom format feature allows four sections, normally used for positive numbers, negative numbers, zero, and text. There is no section for Blank, and the text section of custom formats isn't applied to blank cells. Only conditional formatting can be used to format specifically blank cell only.


Compared to ZLS

For most practical purposes, blank cells are assumed to contain a ZLS. The most general way to test for the absence of any information is to test against "":

    = IF(A1="", "", B1/A1)

The formula above is used to avoid the #DIV/0! error when dividing by an empty cell. When A1 is blank, the test A1="" evaluates to True, and nothing is displayed. The side effect is that the cell containing the formula will never be Blank. There is no way in Excel to have an expression return "Blank", not even using a user defined function. Since expressions returning nothing have to return ZLS, it makes perfect sense to check against ZLS to determine if the cell is blank. Of course, ISBLANK(A1) will return false if A1 contains an expression returning a ZLS.

When sorting a column, ZLS will come first, before any non-blank cell, but blank cells will be sorted last. This is one of the few cases where blank isn't equivalent to ZLS, but then again, ZLS can only result from a formula.


Compared to Null

To reproduce the behaviour of Null in Excel, the closest equivalent would be #N/A (or =NA() -- a weird but popular complication). In fact, this is an error condition and is hence somewhat "stronger" than Null, but the basic rule of Null is respected: any expression involving #N/A is also #N/A. Like Blank, #N/A is impervious to formatting, but a "blank" appearance can be simulated with conditional formatting using ISNA() and white-on-white formatting.



About Null

Unlike untouched cells or variables, untouched fields in Access and other SQL engines are "Null". Working with Null is one of the challenges that every Access developer has to face. To determine nullity, VB offers:

    If IsNull(A) Then

However, it's not always necessary to check for Null: it's also possible to use the behaviour of Null in cleverly constructed expressions. This will be explored shortly, but first it's important to mention that Access SQL uses another, faster syntax:

    A Is Null
    A Is Not Null
    Not A Is Null

The 'Is' construct is specific to SQL, and is totally different from 'Is' in VB (see the section About Nothing). Although IsNull() can be used in a query expression, it's a library function call, while 'Is Null' is evaluated directly by Jet and is thus much faster.

The value Null is said to "propagate", in that (almost) every expression containing Null evaluates to Null. This can be tested in queries, but also in VBA's immediate pane; the expressions below all return Null.

    ? Null + 2
    ? "Hello " + Null
    ? 1 / Null
    ? Len(Null)
    ? Str(Null)
When the expression contains function calls, the problems start. Most string functions accept and can return Null, but most mathematical functions cannot. This is easy to determine by observing the "parameter info" -- menu (Edit | Parameter Info) or Ctrl+Shift+I -- which normally appears after typing the opening bracket of a function. If it reads, for example "Text As String", the argument Text cannot be Null. If it reads just "Text", the argument is a Variant and will accept Null. Try it with the functions Trim() and Trim$(); functions ending with a '$' only work with strings. None of the following will work either:

    ? Sin(Null)
    ? Left$(Null, 1)
    ? AscW(Null)
    ? CCur(Null)

Data conversion functions like CCur() are the most problematic. They seem to accept variants (out of necessity), but raise an internal error when called with Null. The safe conversion functions include: Int(), Str(), and CVDate(). CVDate() is particularly useful, as text to date conversions are quite frequent and often applied to columns with Null fields. A similar function would be useful to convert to numeric, but there is a simple workaround:

    B = A + 0   ' simulates B = CVDbl(A)

If A is Null, so will be B. If A is a string representation of a number, B will be that number, thanks to implicit conversion, which occurs when '+' is used with a number on either side.

Access (but neither VB nor Excel) provides one other function to deal with Null: Nz() for "null to zero".

    A = B + C
    A = Nz(B) + Nz(C)

The first expression will assign Null to A if B or C are Null. The second will always assign a number to A, by converting Null values to 0. Nz() can also handle strings, and it has a second argument to be used when the first is Null.

    datSearch = Nz(A, Now())   ' uses Now() if A is Null
    A = Nz(B, Nz(C, Nz(D, 100)))   ' assigns B, C, D, or 100 to A
    strCateg = Nz(A, "(missing)")   ' Categ will be A, or "(missing)"

There are three operators that do not propagate Null:

The Or Operator

In order for criteria to work as expected, 'Or' is semi-resistant to Null. The following Access SQL and corresponding VBA expressions would not work otherwise:

    A Is Null Or A > B
    IsNull(A) Or A > B

When A is indeed Null, the left portion will be True but the right portion will be Null. However, the operator is only semi-resistant:

    Null Or True  =>  True
    Null Or False  =>  Null

This isn't a problem, because, when used as a criteria, in a VB If construct, in IIf(), or in Switch(), Null is assimilated to False.

The '&' Concatenation Operator versus '+'

The & operator takes a global pre-emptive measure: it performs a forced string conversion on both sides before the concatenation. Null becomes a ZLS.

    123 & #2009-12-24#  =>  123<local string representation of the date>
    2e3 & Null  =>  2000
    'X' & Null & 'Y'  =>  'XY'

This is quite different from the other concatenation operator: '+', which does propagate Null, just like in normal additions.

    123 + Null  =>  Null
    "abc" + Null  =>  Null
    123 + " m²"  =>  Error: Type mismatch!
    Str(123) + " m²"  =>  " 123 m²"
    Str(Null) + " m²"  =>  Null   ' compare with:
    Format(Null, "0 m²")  => ""

By carefully choosing either '&' or '+', it is possible to create conditional string expressions, which will behave correctly when some values are Null.

    FullName = LastName & ', '+FirstName   ' compact expression equivalent to:
    FullName = LastName & IIf(FirstName Is Not Null, ', ' & FirstName)

The comma will be added only if a FirstName is present. More complex expressions can be constructed, which would almost require a user-defined function otherwise.

    FullName = LastName & ','+(' '+FirstName & ' '+MiddleInitial) & ' ('+Title+')'

Only the last name is required, all other fields can be Null, and the full name will be typographically correct (try it!)

The Is Null construct, IsNull() function, etc.

The special "Is Null" syntax (and the reverse "Is Not Null"), will never return Null. Likewise, IsNull(), Format(), Nz(), IIf(), and a few others can be used to fence against Null propagation.

It should be pointed out that the Jet SQL syntax "Is Null" is much faster than calling the VB function IsNull(). Likewise, control source expressions on forms and reports should also use "Is Null", since they are interpreted by Jet, and not VBA.


Compared to ZLS

In a well constructed database, there should never be any conflict between Null and ZLS. The preferred way to identify "blank" fields is to use Null. Furthermore, the Access interface will not let the user enter a ZLS into a field or a control, so ZLS values will only occur by accident, while importing data from an external source, or through careless programming.

Ideally, all text fields should have the property "allow zero length: no", except for very special cases. Alas, the default value is "yes", so that almost all code published on Experts-Exchange needs to fend against the possibility of ZLS in text fields.

    Required: No; Allow ZLS: Yes  -- default: both Null and ZLS are accepted: BUG NEST!
    Required: No; Allow ZLS: No  -- best setting for nullable fields; no ZLS
    Required: Yes: Allow ZLS: No  -- for required fields, including key fields
    Required: Yes: Allow ZLS: Yes  -- special case: blank fields are ZLS

There is no simple way to change the default settings for new fields; I consider this a serious flaw in Access, almost a bug. If not, it's the source of many bugs...



About Nothing

Since Visual Basic for Applications has to interface with objects from the calling application and other libraries, "object variables" are needed. They can be generic or specific to one object type from a library.
    Dim obj As Object
                          Dim xlWB As Excel.Workbook
                          Dim frm As Access.Form
                          Dim fld As DAO.Field

Open in new window

Again, what is the value of an object variable before it's been "Set" to an actual object? It's a form of zero, of course, sometimes called the "null pointer" or "nil". In VB, the keyword "Nothing" has been selected.
    Let A = B   ' writes a copy of B into A
                          Set A = B   ' writes the address of B into A

Open in new window

An object variable without an object points to Nothing. The first time I wanted to test for nothingness, I tried quite naturally:

    If IsNothing(obj) Then   ' COMPILE ERROR!

No luck! I attempted to write my own until I discovered that VB has a special operator to deal with objects: "Is". This is quite different from the "Is Null" syntax of Jet SQL. "Is" compares two objects; technically it compares the memory address held by two pointers.

    If obj Is Nothing Then

Incidentally, the specialization of this operator explains the error message when one tries to use the "Is Null" syntax in VB: "Object required!", instead of a more friendly "The syntax 'Is Null' isn't valid in VB, please use IsNull()."

There would be much more to say about objects, but this is not the place. I will simply mention some ado about:

    Set obj = Nothing

It writes the address 0 into the variable 'obj', that's all. The side effect is, or might be, that the object is no longer "held" by any variable, and the object can be discarded by a background process called "garbage collecting", freeing unused memory.

Perhaps a better choice for 'Is' and 'Set' in VB would have been 'Has' and 'Give'. "If Charly Has Nothing Then Give Charly New Balloon".
Sub Zoo()
                          
                          Dim Charly As Object                ' Charly is created
                          Dim Anna As Object                  ' and his sister Anna
                          
                          Set Charly = New Balloon            ' Create a balloon for Charly,
                          Charly.BalloonInflate               ' inflate it,
                          Charly.BalloonColour = vbRed        ' and make it red.
                          
                          Set Anna = New Balloon              ' Create another balloon for Anna
                          Anna.BalloonInflate                 ' inflate it,
                          Anna.BalloonColour = vbBlue         ' and make it blue.
                          
                          Set Charly = Nothing                ' Charly lost the balloon, it's gone with the wind
                          
                          Set Charly = Anna                   ' Charly grabs a string to Anna's ballon
                          Charly.BallonBurst                  ' The blue balloon is destroyed
                          Set Anna = Nothing                  ' Anna lets go of her string
                          
                          Set Anna = New Balloon              ' Create a new balloon for Anna
                          Charly.BalloonInflate               ' inflate it,
                          Anna.BalloonColour = vbPink         ' and make it pink.
                          
                      End Sub
                      ' When the subroutine exits, local variables go out of scope:
                      ' Charlie and Anna disappear,
                      ' the busted blue balloon washes down the drain,
                      ' the pink balloon flies off.

Open in new window

(scroll...)
' Another ending:
                          
                          ' "properly" release objects:
                          Set Charly = Nothing                ' The blue balloon is washed down the drain.
                          Set Anna = Nothing                  ' The pink balloon is released and flies off.
                          
                      End Sub

Open in new window

In this "Zoo story", both endings are similar. Whether to explicitly set objects to Nothing or to let the variables holding them go out of scope is really only a matter of style. If you get involved in a dispute over the question "do you have to set object variables to Nothing?": scroll down... or make sure you have a good understanding of variable scopes, cross-referenced objects, and garbage collection, before getting involved... My stand? It's all obscurantism.



About Missing

Our last guest in this article: the missing argument. An argument can only be missing if it's optional in the first place, or the compiler will complain about it. It also needs to be a variant, and have no default value.
Function Root(Number As Double, Optional Indice)
                          If IsMissing(Indice) Then Indice = 2
                          Root = Number ^ (1 / Indice)
                      End Function

Open in new window

The developers of Visual Basic could very well have opted to treat optional missing arguments like uninitialized variables. In that case, Indice would have been Empty -- an uninitialized Variant. However, there are cases where passing a Variant, which happens to be Empty, is a valid argument, so yet another keyword for absence was needed.

A better declaration of Root() would type the second argument, and also provide a default value. This would be consistent with built-in mathematical functions.
Function Root(Number As Double, Optional Indice As Double = 2) As Double
                          Root = Number ^ (1 / Indice)
                      End Function

Open in new window


Technically, "missing" is an error condition, which explains why typed arguments cannot have that value. Variants can hold all sorts of different data types, one of which is errors. Given this function:
Function Echo(Optional arg)
                          Echo = arg
                      End Function

Open in new window


The following expressions return:

    IsMissing(Echo())  =>  True
    IsError(Echo())  =>  True
    Echo()  =>  Error 448

There must be something more to it, however, because:

    ? IsMissing(CVErr(448))  =>  False (!)

In most cases, there are easier ways to handle missing arguments, especially if some arguments can conceivably also be Null or Empty. For example, these are robust implementations of Root() for Excel and Access:
' Root(Number [, Indice]) -- For Excel
                      '
                      ' Extracts the square root (default) or the Indice's root of Number.
                      ' Returns #DIV/0! if Indice = 0.
                      '
                      Function Root(Number As Double, Optional Indice As Double = 2)
                          If Indice = 0 Then Root = CVErr(2007) Else Root = Number ^ (1 / Indice)
                      End Function

Open in new window

Excel functions don't need to anticipate Null; blank cells are Empty, and are converted to zero when used as an argument. However, the return value should be a Variant so that an error can be returned.
' Root(Number [, Indice]) -- For Access
                      '
                      ' Extracts the square root (default) or the Indice's root of Number.
                      ' Returns Null if either argument is Null or if Indice = 0.
                      '
                      Function Root(Number, Optional Indice = 2#)
                          
                          Root = Null
                          If Not IsNumeric(Number) Then
                          ElseIf Not IsNumeric(Indice) Then
                          ElseIf Indice <> 0 Then
                               Root = Number ^ (1 / Indice)
                          End If
                          
                      End Function
                       
                      ' alternative:
                      '
                      Function Root(Number, Optional Indice = 2#)
                      On Error Resume Next
                          Root = Number ^ (1 / Indice)
                          If Err Then Root = Null: Err.Clear
                      End Function

Open in new window

Robust Access functions should handle Null gracefully, and also return Null in case of error. Jet cannot handle error codes, and Null is hence the general "not available", "can't do", "failure", ... return value.

As can be seen, it's simpler to test for a certain data type -- IsNumeric(), IsDate() -- than to test explicitly for the "missing" error condition.



The Variant Data Type

The Variant data type is totally versatile: a Variant can be Empty, Null, ZLS, Nothing, Missing, #N/A, #DIV/0!, and much more. If you really don't know what a given variable contains, your best chance is to ask for the "type name"...

The following function accepts Anything, and makes fun of everything.
Function Knock(Optional Anything) As String
                          
                          Knock = TypeName(Anything)
                          Select Case Knock
                          
                          Case "Null", "Empty", "Nothing"
                              Knock = "Nobody's home..."
                              
                          Case "String"
                              If Anything = vbNullString _
                              Then Knock = "There is no message." _
                              Else Knock = "A note says """ & Anything & """"
                              
                          Case "Error"
                              If CLng(Anything) = 448 _
                              Then Knock = "There is no door." _
                              Else Knock = "Wrong number: " & CLng(Anything) & "."
                              
                          Case Else
                              If Knock Like "[AEIOU]*" _
                              Then Knock = "An " & Knock & " greets you." _
                              Else Knock = "A " & Knock & " greets you."
                              
                          End Select
                          
                      End Function

Open in new window

It's mildly entertaining, but surprisingly useful!



Conclusion

Even if you leave Empty handed, I hope you had a laugh or two. If Nothing else, please knock the 'Yes' link, or the benefit to me would be Null. If you feel something is Missing, please comment below (allow zero length: no), and if I'm not Absent from the computer, I'll answer in Zero time.

Legal disclaimer: "Bug" is a trademark of its owner; 'Void', 'Absent', 'Undefined', 'Nada', and 'Zilch' are reserved keywords for future extensions; 'Error' and 'Omission' are my own.


Happy computing!

Markus G Fischer
(°v°)

about unrestricted access
27
76,486 Views

Comments (11)

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Markus,

Great article!

Not only informative, but also a fun read.

Author

Commented:
Thanks Miriam! I'm glad you liked it. As you know, I did catch the virus, so there is more to come...

(°v°)
CERTIFIED EXPERT

Commented:
Nice article Markus

Dave
Thank you for an article that helps to fill in some of the gaps, so to speak.
Just a comment on the 'voting buttons' at the end of the article (i.e. the command buttons  polling whether one found the article helpful or not). I don't know if this is due to the new web pages EE has implemented, or maybe it's just my installation (I'm running IE 9.0), but I had a heck of a time trying to vote 'Yes' (disenfranchisement on a petit scale!). I have attached an image showing how the buttons appear on my screen. Note the vote count controls floating over the buttons. I had to slip my mouse edgewise around those 'vote count' controls to get at the 'Yes' button underneath.
Comments buttons

Commented:
Very useful article. I would like to add my two cents:

#N/A in Excel is not just ordinary error condition as it might look from the article. People coming to Excel from world of databases, for whom NULL value in database field is a friend and not an alien find in #N/A the missing piece: #N/A is an Excel equivalent of database NULL value. #N/A symbol in Excel means "no value is available" (check Excel help :)

The fact that I commonly use it this way is not so important compared to the following ones:
#N/A propagates through calculations the same way like NULL does:
2 + #N/A returns #N/A
OR(AND(#N/A, A1), B3) returns #N/A

#N/A stands outside all other error conditions: (actually it is not an error condition)
Although built-in function ISERROR() considers #N/A to be an error, its sister function ISERR() excludes it. So if you decide to detect errors via ISERR(), #N/A can become valid part of your data. Then you can use ISNA() to test for its presence, what is equivalent to IS NULL in SQL world. Also IFNA() brings you back SQL ISNULL() or Access' Nz() functionality.

NA() function produces #N/A value, but its only purpose is to provide compatibility with other spreadsheet programs. Easier way to produce #N/A value is to type it directly into cell.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.