Error Trapping Help

[Al & Cay Grant]

Hi guys,

I have a simple peice of code;

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

age = DateDiff("yyyy", TextBox4, Now)
TextBox5.Value = age

End Sub

It calculates the age in years between NOW and a Date of Birth in Textbox4.

I want to set error handling so that the rest of the procedures still run
if;
(a) Incorrect DOB format is entered -or-
(b) No no DOB entered

Anyone know how to do this? I guess I need something that handles a
datediff error and just keeps running, instead of putting the age in TextBox
5
it will just leave it blank.

Cheers

- Al

 

[Jay Freedman]

Hi, Al,

Instead of error-trapping after the fact, it's better to prevent the error
by checking the input first. In this case, VBA has a built-in function
called IsDate that returns True if the input parameter is a valid date
expression, and False if not. Among the things it rejects are an empty
string, a string of all blanks, and random garbage. It accepts dates in
uu/xx/yyyy format, strings like "September 8, 2003" or "8 September 2003",
and partial dates like "3/22" or "June 3" (which assumes the current year).

If the expression in the textbox isn't a date, simply set the Cancel
parameter to True. This prevents the cursor from leaving the textbox. If you
want, you can add a MsgBox statement before the Exit Sub line to tell the
user why the input was rejected.

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(TextBox4.Value) Then
Cancel = True
Exit Sub
End If
TextBox5.Value = DateDiff("yyyy", TextBox4.Value, Now)
End Sub

 

[Al & Cay Grant]

Right. Thanks for that.

Unfortunately I have discovered that Datediff("yyyy", TextBox4, Now)
does not actually give me the age of the person on todays date?

If the date of birth 12/12/80 for example I get 23 not 22?

- AL

 

[Lars-Eric Gisslén]

Al,

Try to modify your code to something like this:

Dim dDate As Date
Dim nDiff As Long

dDate = #12/12/1980#

nDiff = DateDiff("yyyy", dDate, Now())

If DateSerial(Year(Now()), Month(dDate), Day(dDate)) > Now() Then
nDiff = nDiff - 1
End If

MsgBox nDiff

Reagrds,
Lars-Eric

 

[Doug Robbins - Word MVP]

Hi Al,

If you want the number of whole years, use

Int(Datediff("d", TextBox4, Now)/365.25)

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Al & Cay Grant]

Thanks Doug.

Can you explain why my code did not work?

I see that you have done the calc in days then converted to years.

- Al

 

[Mark Tangard]

Hi Al,

Your code gave the "wrong" answer because you asked it to
give you the number of years between the 2 dates' years:

DateDiff("yyyy", TextBox4, Now)

DateDiff yields the "number of time intervals," which is
not same as the number of time units elapsed. (The help
notes that asking for DateDiff in years between 12/31 and
the following 1/1 gives you '1' even though only a day has
elapsed.)

 

[Bob S]

If you want the number of whole years, use

Int(Datediff("d", TextBox4, Now)/365.25)

I believe that this gives an incorrect age for certain cases involving
leap years...

Bob S