Listing A Function Procedure That Uses Various Date Functions to Calculate a Persons

Function CalculateAge(birthDate As Date) As Byte Dim birthdayNotPassed As Boolean birthdayNotPassed = CDate(Month(birthDate) & "/" & _

Day(birthDate) & "/" & _ Year(Now)) > Now CalculateAge = Year(Now) - Year(birthDate) + birthdayNotPassed End Function

' Use this procedure to test CalculateAge.

Sub TestIt2()

MsgBox CalculateAge(#8/23/59#) End Sub

The purpose of the CalculateAge function is to figure out a person's age given the date of birth (as passed to CalculateAge through the Date variable named birthDate). You might think the following formula would do the job: Year(Now) - Year(birthDate)

This works, but only if the person's birthday has already passed this year. If the person hasn't had his or her birthday yet, this formula reports the person's age as being one year greater than it really is.

To solve this problem, you need to take into account whether or not the person's birthday has occurred. To do this, CalculateAge first declares a Boolean variable birthdayNotPassed and then uses the following expression to test whether or not the person has celebrated his or her birthday this year:

CDate(Month(birthDate) & "/" & Day(birthDate) & "/" & Year(Now)) > Now

This expression uses the Month, Day, and Year functions to construct the date of the person's birthday this year, and uses the CDate function to convert this string into a date. The expression then checks to see whether this date is greater than today's date (as given by the Now function). If it is, the person hasn't celebrated his or her birthday, so birthdayNotPassed is set to True; otherwise, birthdayNotPassed is set to False.

The key is that to VBA a True value is equivalent to -1, and a False value is equivalent to 0. Therefore, to calculate the person's correct age, you need only add the value of birthdayNotPassed to the expression Year(Now) - Year(birthDate).

0 0

Post a comment