The number of types of nothing in VB/VBA can be really confusing, and is tied
up with the various data types.
Null is equivalent to a Null value in a database field. Null is not equal to
anything else including Null, and most operations performed on Null with
another value result in Null - the exception being the explicit string
concatenation operator & which treats Null the same as a blank string. Null
also acts like its own data type with only one possible value, Null.
1 + Null gives Null
"A" + Null gives Null
"A" & Null gives "A"
(Null = Null) gives Null
(0 = Null) gives Null
If 0 = Null Then ... raises invalid use of Null error
If IsNull(Null) ... executes the code in the If block
IsNull(Empty) gives False
Len(Null) gives Null
Empty is the default value for a Variant variable, and is interchageable for
various other default values. Used in arithmetic, it acts as a zero. Used in
string operations, it acts as a blank string, etc. Like Null, Empty also acts
like a data type with one possible value, Empty. This is compatable with the
behavior of BASIC for an uninitialized value.
1 + Empty gives 1
"A" + Empty gives "A"
"A" & Empty gives "A"
(Empty = Empty) gives True
(0 = Empty) gives True
("" = Empty) gives True
If 0 = Empty Then ... executes the code in the If block
IsEmpty(Empty) gives True
IsEmpty(Null) gives False
Len(Empty) gives 0
vbNullString is actually a string that points to address zero, and it was
created for the purpose of passing a null string pointer to a DLL call. Folks
have discovered, though, that VB/VBA treats this as equivalent to the empty
string "", but processes it faster in some cases.
(vbNullString = "") gives True
IsNull(vbNullString) gives False
IsEmpty(vbNullString) gives False
Len(vbNullString) gives 0
One difference between vbNullString and Null is that vbNullString (or "") is a
string type. String variables can only hold string data, so a string variable
can hold these values, but it cannot hold Null or Empty because these are not
string type data.
Missing is a special value/type a Variant can have indicating that an optional
parameter was not supplied. There's very little you can to with a Missing
other than check if it is missing using IsMissing without generating an error,
however, you can assign it to another variant, and you can pass it to an
optional argument of another procedure. Since strings can contain only string
type data, they cannot have a value of Missing.
Nothing is an object reference that's not referencing any object. Since a
Variant can contain any object reference, it can contain Nothing, meaning it
has an object reference, but that reference is not to any existing object.
Any object variable (such as dbs As DAO.Database) can contain either a
reference to an object of its specific type or Nothing, meaning it has not
been initialized or it has been cleared by setting it back to Nothing.
Object references are not values, and if you try to treat them as values, VB
will try to find a default value property of the object to use as its value.
Since Nothing does not point to an object at all, it can never be treated as a
Value, but you can do pretty much anything you want with it as a refrence
including set some other Variant or object variable to its reference (which is
Nothing). The "Is" operator is used to compare 2 references to each other,
and using "Is" to compare 2 Nothing references gives True.
(Nothing Is Nothing) gives True
(Nothing = 0) raises "Invalid use of object) error
(New VBA.Collection Is Nothing) gives False
On Sat, 7 Aug 2004 19:37:15 +0100, "Gerry Abbott" <pl****@ask.ie> wrote:
Thanks Dimitri,
vbNullString worked fine
I had tried Isnull(arguament name) but that did not work
Can someone explain whats the difference betweeen the Isnull function and
the vbnullstring test. ?
Regards,
Gerry
"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1.. . On Aug 07 2004, 08:26 am, "Gerry Abbott" <pl****@ask.ie> wrote in
news:DT******************@news.indigo.ie:
>
> Hi All,
>
> Im trying to use thie combination but have not had success. Below is
> the function
> It tried the following
>
> myriskLevel(2,2)
> myrisklevel(0,0,2)
>
> and the ismissing(Three) alwasy returns false.?
>
> Am is missing something here?
>
> Thanks,
>
>
>
> -----------------------------------------------------------------------
> ----- ----------------
> Public Function myFunctionl(One As String, Two As String, Optional
> Three As String) As String
<snip>
IsMissing() function only works for optional arguments declared as
Variant. So you may want to change your function signature as
Public Function myFunctionl(One As String, Two As String, Optional
Three As Variant) As String
If the argument is declared as a string, test for its equality to
vbNullString instead of using IsMissing().
--
remove a 9 to reply by email
