INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Thanks so much for having a place for us propeller heads to hang out and chat.
Where in the world do Tek-Tips members come from?
Office / VBA General
Nulls and Other Nothings - An Explanation
Posted: 11 Jun 03 (Edited 8 May 04)
It is easy to be confused by the various ways in which a lack of data can be represented in various situations. Terminology, both official and ‘unofficial’, doesn’t help - for example “null string” is a contradiction in terms – strings exist and are, therefore, not null. What follows is a slightly expanded version of a reply I gave to a question which offers some explanation.
At a very simple level, things can either exist or not. If they exist they may or may not contain meaningful data. Things which do NOT exist may be null, nothing or missing. Things which do exist but have no meaningful content may be empty.
Taking each in turn:
- 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.
Very briefly, as this forum is not generally Access-specific, fields returned from Queries, and controls in both Forms and Reports have the potential to be null and code which references them should check for this unless it is known that they always exist – they are, or are bound to, for example, Required fields.
I am not aware of anywhere in either Word or Excel where nulls occur naturally but Excel confuses the issue by having a #NULL! error which can be raised if reference is made in a worksheet formula to the intersection of two ranges which do not intersect (in VBA the result is Nothing).
To check for something being null, use the IsNull function. There is a NULL keyword but it cannot be used in comparisons. "If Var1 = Null" will ALWAYS return FALSE regardless of Var1.
- 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.
Note that typed variables (that is, all variables other than variants) which do not refer to objects and which have not been set, are initialised in VBA, depending on their type, to either zero or a zero-length string, but are not empty (or null).
- Nothing is a slightly different animal. Object variables which do not currently reference an object have a ‘value’ of Nothing. More importantly, 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.
To check if an object reference is nothing, use the construct: If Object_var Is Nothing
- Finally, Missing. This only refers to Optional parameters. If an optional parameter to a function is declared with a type (e.g. "Optional Var1 As String") 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 (declared as "Optional Var2" or "Optional Var2 As Variant"), and will only be Missing if it is not supplied by the calling code. To check if a parameter is missing use the IsMissing function.
Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close