Variable HasValue?

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

How can I check if a variable already has a value or if it's still blank?

Thanks,
Lars
 
Lars said:
How can I check if a variable already has a value or if it's still blank?

Thanks,
Lars
Click to expand...

Depends on what the data type is.

If we're talking about primitive data types (e.g. string, integers,
double, date), then they have a default initial value (e.g. "", 0, 0,
1899-12-31, respectively)

You could test for those values but be aware they could have been
assigned a new value that is same as the default value and you would
have no way to know, unless you check the source where you set the new
value.

For objects such as DAO/ADO Recordset, Excel.Application, they are
initially given a Empty value. You test an object variable with
IsEmpty(). Note that IsEmpty will succeed even if the object has been
set to nothing because Nothing is not same thing as Empty.

HTH.
 
Thanks. Can't see why I've missed that.
Lars

Banana said:
Depends on what the data type is.

If we're talking about primitive data types (e.g. string, integers,
double, date), then they have a default initial value (e.g. "", 0, 0,
1899-12-31, respectively)

You could test for those values but be aware they could have been assigned
a new value that is same as the default value and you would have no way to
know, unless you check the source where you set the new value.

For objects such as DAO/ADO Recordset, Excel.Application, they are
initially given a Empty value. You test an object variable with IsEmpty().
Note that IsEmpty will succeed even if the object has been set to nothing
because Nothing is not same thing as Empty.

HTH.
Click to expand...
 
this usually works...
If NZ(trim(myvariable),vbnullstring) = vbnullstring then ...
<its has no value>
else
<it has a value>
end if
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Banana said:
For objects such as DAO/ADO Recordset, Excel.Application, they are
initially given a Empty value. You test an object variable with IsEmpty().
Note that IsEmpty will succeed even if the object has been set to nothing
because Nothing is not same thing as Empty.
Click to expand...


You are mistaken here, or else I am misunderstanding what you are trying to
say. Object variables initially have a value of Nothing, and IsEmpty will
report False for an unassigned object variable. To see if an object
variable has been set, use the Is Nothing condition; for example:

Dim rs As DAO.Recordset

If rs Is Nothing Then
' rs has not been set, or has been most recently set = Nothing
End If

Now, Variant variables are initially Empty, and can be tested using the
IsEmpty function:

Dim v As Variant

If IsEmpty(v) Then
' v has not been assigned any value.
End If
 
Dirk said:
You are mistaken here, or else I am misunderstanding what you are trying
to say. Object variables initially have a value of Nothing, and IsEmpty
will report False for an unassigned object variable.
Click to expand...

I stand corrected. I somehow got into the trap of thinking Empty were
for objects, not variants (which isn't anything like objects even if it
can act like one.)

Thanks.
 
this usually works...
If NZ(trim(myvariable),vbnullstring) = vbnullstring then ...
<its has no value>
else
<it has a value>
end if
Click to expand...

That's not terribly versatile code -- it will only be useful with
variants, which are the only data type that can be Null.

To test a string value to see if it has anything stored in it, test
if Len()=0. According to Michael Kaplan, this is faster than
comparing to vbNullString (it doesn't matter in a single test, but
if you're testing in a loop, it *will* make a noticeable difference,
so I think it's best to always use the most efficient approach even
when it won't make a real-world difference -- it's good to have a
habit so that you don't get it wrong when you need the performance
improvement).

To test numeric variables, test if they are 0. This includes
Intenger, Long, Double, Single, Currency, Boolean and Date.

To test variants, use IsEmpty().

For object variables (Recordset, Database, Form, Control, etc.),
test if they are Nothing:

If rs Is Nothing Then

And as others have advised, just because a string has length 0 or a
numeric variable has the value 0 doesn't mean they aren't storing a
valid value.
 
Lars said:
Why is this better than
if str = ""
?

Thanks,
Lars
Click to expand...

This is pure conjecture on my part:

str = "" is a comparison operation whereas Len(str) = 0 is measuring the
length without any comparison. Comparing two things are going to be more
expensive than simple measurement.

Anyway, if you want the proof, you could set up a simple loop to repeat
the operations several times, and use some precise timing mechanism via
API (capable of measuring milliseconds, something that Access Date data
type cannot) to validate this for yourself.

HTH.
 
You're right. I did a quick test with the code beneath where the multiple
<if str = ""> takes 34 seconds and the multiple <if len(str) = 0> takes 15
seconds. That is with billion repitions.

Thanks,
Lars

Function fHasValTest()
Dim l As Long
Dim str As String
Dim dt As Date

dt = Now
For l = 1 To 1000000000
If Len(str) = 0 Then
End If
Next
MsgBox DateDiff("s", dt, Now)

dt = Now
For l = 1 To 1000000000
If str = "" Then
End If
Next
MsgBox DateDiff("s", dt, Now)

End Function
 
Why is this better than
if str = ""
Click to expand...

Well, for one, comparing to "" allocates memory for the empty
string. Comparing to vbNullString would be more efficient, because
the memory for that system constant has already been allocated.

If I recall MichKa's explanation it was that the comparison of Len()
to 0 was more efficient than comparing two string values. I don't
remember that he explained the details of why that was so.
 
I did a quick test with the code beneath where the multiple
<if str = ""> takes 34 seconds and the multiple <if len(str) = 0>
takes 15
seconds. That is with billion repitions.
Click to expand...

You should also test str = vbNullString. My be is that it will be
substantially faster than comparing to "".
 
Back
Top