Empty, Nothing, Null, IsEmpty, IsNull, IsMissing, Optional 3

[krinid]

Can anyone aptly explain the differences/uses of Empty/Null/Nothing and the relevant functions IsEmpty, IsNull, IsMissing?

In particular, as related to optional parameters and function return values.

As in the following code, it seems that whether optional values are passed in or not, they are not Empty, Null or Missing. Similarly for function return values, whether a valid object is passed back or not, it's neither Empty or Null.

PROGRAM OUTPUT:
---(Function Parameters [Passed In])
IsMissing(I) = False
IsMissing(S) = False
IsMissing(C) = False
IsEmpty (I) = False
IsEmpty (S) = False
IsEmpty (C) = False
IsNull (I) = False
IsNull (S) = False
IsNull (C) = False
---(Function Parameters [Not Passed In])
IsMissing(I) = False
IsMissing(S) = False
IsMissing(C) = False
IsEmpty (I) = False
IsEmpty (S) = False
IsEmpty (C) = False
IsNull (I) = False
IsNull (S) = False
IsNull (C) = False
---(Function Return Value)
IsEmpty (Q) = False
IsNull (Q) = False


CODE: (execute the controller subroutine)
Sub controller()
Dim Q As New Collection

Debug.Print "---(Function Parameters [Passed In])"
Set Q = returnNothing(1, "S", Q)

Debug.Print "---(Function Parameters [Not Passed In])"
Set Q = returnNothing()

Debug.Print "---(Function Return Value)"
Debug.Print "IsEmpty (Q) = "; IsEmpty(Q)
Debug.Print "IsNull (Q) = "; IsNull(Q)
End Sub

Function returnNothing(Optional I As Integer, Optional S As String, Optional C As Collection) As Collection
Debug.Print "- - -"
Debug.Print "IsMissing(I) = "; IsMissing(I)
Debug.Print "IsMissing(S) = "; IsMissing(S)
Debug.Print "IsMissing(C) = "; IsMissing(C)

Debug.Print "IsEmpty (I) = "; IsEmpty(I)
Debug.Print "IsEmpty (S) = "; IsEmpty(S)
Debug.Print "IsEmpty (C) = "; IsEmpty(C)

Debug.Print "IsNull (I) = "; IsNull(I)
Debug.Print "IsNull (S) = "; IsNull(S)
Debug.Print "IsNull (C) = "; IsNull(C)
End Function

 

[jeffman38]

I wondered this myself and found reference at:

http://www.subzero-solutions.com/DevCorner/DC008.htm

TEXT BELOW >>> HOPE IT HELPS.

DON'T CONFUSE NULL, NOTHING, "", EMPTY, AND ZERO.
There are many ways in VB to say that something is "nil," a term I'm going to use temporarily to collectively indicate zero, uninitialised blank, empty, and so on. Each of these terms is subtly different. For instance, it might seem picky to distinguish between an empty string, an uninitialised variable, an ASCII zero, and a null pointer. But these differences affect many things, from database storage to calling external functions. Complicating the situation is that other languages-C in particular-often handle similar constructs differently.
Sometimes VB knows what's right and raises an error when it encounters code that tries to assign Null to a string:

Dim sName as String
sName = Null

But more often, a programmer tries to find out if something is "nil" by testing it against a known value (zero, "", False, or a constant) or using a built-in testing function such as IsEmpty or IsNull. Sometimes the results are not what was expected. For example, setting an object pointer to Nothing is not the same as making it null. The test in this code will always return False:
Dim obj as Object

Set obj = Nothing
If IsNull(obj) = True Then…

And, of course, the only thing possibly worse than buggy code is getting flamed for using the wrong term in public. To keep from being embarrassed, you should know these terms:
"": A zero-length string (commonly called an "empty string&quot is technically a zero-length BSTR that actually uses six bytes of memory. In general, you should use the constant vbNullString instead, particularly when calling external DLL procedures. Empty: A variant of VarType 0 (vbEmpty) that has not yet been initialised. Test whether it is "nil" using the IsEmpty function. Nothing: Destroys an object reference using the Set statement. Test whether it is "nil" using the Is operator:

If obj Is Nothing Then...

Null: A variant of VarType 1 (vbNull) that means "no valid data" and generally indicates a database field with no value. Don't confuse this with a C NULL, which indicates zero. Test whether it is "nil" using the IsNull function. vbNullChar: A character having a value of zero. It is commonly used for adding a C NULL to a string or for filling a fixed-length string with zeroes:
Path = String(255, vbNullChar)

vbNullString: A string having a value of zero, such as a C NULL, that takes no memory. Use this string for calling external procedures looking for a null pointer to a string. To distinguish between vbNullString and "", use the VBA StrPtr function: StrPtr(vbNullString) is zero, while StrPtr("&quot is a nonzero memory address

 

[TonyJollans]

Hi krinid,

I hesitate to reply after jeffman38's long post, but I think his reply is rather more wide ranging than your question. As you ask in a VBA forum I assume you want VBA-specific information.[ol][li]Null is a relational database concept which represents total absence of data in a repository being queried. VBA implements a method of dealing with nulls which can sometimes be confusing but, unless you specifically "create" them, they rarely exist in Office Applications other than Access. As this forum is not generally Access-specific, I will say no more about them at the moment.[/li]

[li]Empty is, I'm afraid, another slightly confusing term. A cell in an Excel worksheet can be empty and can be checked as such by using the IsEmpty function in VBA (but note that in a worksheet you use ISBLANK to test for the same condition). A variant (i.e. untyped) variable can also be empty meaning un-initialised and can also be checked with the IsEmpty VBA function.[/li]

[li]Nothing is slightly different. Setting an object variable to Nothing doesn't just affect the variable; if it is the only variable referencing the particular object, it disassociates the object from the code and releases all related system resources.[/li]

[li]Finally, optional parameters. If you type an optional parameter to a function (e.g. "Optional Var1 As String&quot then, if it is not supplied by the calling code, VBA provides a value of the appropriate type - zero for numeric types, a zero-length string for string types and it will NEVER be Missing. A parameter can only be Missing if it is untyped ("Optional Var2" or "Optional Var2 As Variant&quot).[/li][/ol]I have tried to keep the explanation simple, yet accurate. Hope it helps; if you want further explanation please post back.

Enjoy,
Tony

 

[xlbo]

Tony - very eloquent and useful post - have a star....and have you considered creating this as a FAQ ???
If not, I'd be happy to use this post and create a FAQ (full credit to you of course ;-) )
Jeffman38 - The only reason I gave Tony a star and not you is because he has specifically given the answers relevant to this forum

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TonyJollans]

Geoff,

It never crossed my mind that I had enough knowledge to write something as general as a FAQ, but I'd be happy to give it a go.

Thanks for the star,
Tony

 

[Loomah]

Tony
If I can write a FAQ then anyone can - you don't need a huge amount of knowledge!!!!! Getting people to refer to them - that's another matter!

Please go with the FAQ, it'll save me copying this thread out for future reference!

Purple Pointy Pip from me too.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Tony - to create the FAQ, all you need to do is go to the FAQs area and start a new one
Coupla sentances to explain what the FAQ is about and then, I'd suggest, you just copy your post wholesale into it - easy as that

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TonyJollans]

I have expanded a little on my post and submitted it as a FAQ. Hope it helps people.

Thanks for the star, Loomah, and no, I haven't got a job yet. Haven't got time for one.

Enjoy,
Tony

 

[krinid]

TonyJollans,
I understand all the concepts/terminology but come from a C background, and wasn't sure how this all worked itself out in VB (particularly such issues I hadn't even considered like VB assigning default [0 or empty string] values to optional parameters not passed in!) and hadn't found a good summary up until now. You've cleared up a lot of issues for me--indeed, thank you very much. I've given you a star for your brilliant explanation. The FAQ is excellent!

 

[krinid]

New question:
How can I detect whether a Collection has been passed into a function if it's declared as an optional Variant?

function zoobers (Optional coll)
if IsMissing(coll) then
' maybe this is a collection... maybe it's not
' is there a way to detect the type of a variable?
' <process the collection here>
end if
.
.
.
end function

 

[TonyJollans]

Hi krinid,

It's preferable to start a new thread with a new question, but as it's here ...

You can use either:

If TypeOf coll Is Collection

or:

If TypeName(coll) = "Collection"

to check for a collection (or any other type). But note that collection is something declared as Collection. The various built-in collections have their own types (e.g. Sheets).

Enjoy,
Tony