Age from DOB formula

[davidboutche]

I'm using a userform to capture a date of birth. Is there a formula I can use to work out their age and place it into a bookmark based on the current date?

Thanks

[macropod]

Hi David,

Here's a stand-alone macro that does the age calculation:
Sub CalcAge()
Dim BirthDate As Date
Dim Years As Integer, Months As Integer, Days As Integer, DaysInMonth As Integer
BirthDate = InputBox("Please enter your date of birth.")
If (Month(Date) = 2) Then
DaysInMonth = 28 + (Month(Date) = 2) * ((Year(Date) Mod 4 = 0) + (Year(Date) Mod 400 = 0) - (Year(Date) Mod 100 = 0))
Else
DaysInMonth = 31 - (Month(Date) = 4) - (Month(Date) = 6) - (Month(Date) = 9) - (Month(Date) = 11)
End If
Years = Year(Date) - Year(BirthDate) + (Month(Date) < Month(BirthDate)) + (Month(Date) = Month(BirthDate)) * (Day(Date) < Day(BirthDate))
Months = (12 + Month(Date) - Month(BirthDate) + (Day(Date) < Day(BirthDate))) Mod 12
Days = (DaysInMonth + Day(Date) - Day(BirthDate)) Mod DaysInMonth
MsgBox "Your age is " & Years & " years " & Months & " months " & Days & " days "
End SubModify to suit your needs.

[davidboutche]

Thanks for that.

I copied that code into the userform, changed it slightly so that it picked the date of birth from the userform instead of the input box, and then ran some code after it to put the 'years' return, into a bookmark on the active document.

Thanks

Works a treat!

[fumei]

If you are passing a parameter (in this case from someplace on your userform) you may as well make it a Function.
Option Explicit
Function CalcAge(BirthDate As Date) As String

Dim Years As Integer, Months As Integer
Dim Days As Integer, DaysInMonth As Integer

If (Month(Date) = 2) Then
DaysInMonth = 28 + (Month(Date) = 2) * _
((Year(Date) Mod 4 = 0) + (Year(Date) Mod 400 = 0) _
- (Year(Date) Mod 100 = 0))
Else
DaysInMonth = 31 - (Month(Date) = 4) - _
(Month(Date) = 6) - (Month(Date) = 9) - _
(Month(Date) = 11)
End If
Years = Year(Date) - Year(BirthDate) + (Month(Date) _
< Month(BirthDate)) + (Month(Date) = _
Month(BirthDate)) * (Day(Date) < Day(BirthDate))
Months = (12 + Month(Date) - Month(BirthDate) + _
(Day(Date) < Day(BirthDate))) Mod 12
Days = (DaysInMonth + Day(Date) - Day(BirthDate)) _
Mod DaysInMonth
CalcAge = "Your age is " & Years & " years " & _
Months & " months " & Days & " days "
End Function

' EXAMPLE
Sub Yadda()
MsgBox CalcAge(#12/20/1953#)
End Sub

That way you can just put it in your toolbox of code, to be called whenever you need it.

Note that even if you pass "December 20, 1953" to the Function it will work properly.

Private Sub CommandButton1_Click()
Label1.Caption = CalcAge(TextBox1.Text)
End Sub
"December 20, 1953" input into Textbox1, will put "Your age is 55 years 7 months 0 days " into the Label.Caption. You do not need to use the #.......# use of literals in the Yadda example.

underscore character please.

[davidboutche]

Thanks for that. I gave that a try and it worked well. I'm a bit of a novice so i'm learning as I go and working out vb from examples using logic.

If I've got this right, placing your bit of code into a module makes it a function. You can call the function what you want and in this case you called it calcage.

The function called calcage has after it the '()'. Inside them, it declares what is should expect to find. In this case, something called birthdate and that it will be a date....

It then does it's maths, and returns the answer to the function... calcage = the actual age based on the formula. I've told it, calcage = Years.

Then in my form I've included this in my code:
Set bmRange = ActiveDocument.Bookmarks("agebook").Range
bmRange.Text = CalcAge(DOBBOX.Text)
ActiveDocument.Bookmarks.Add _
Name:="agebook", _
Range:=bmRange


From what I understand.. it will insert into my bookmark the function CalcAge based on my information stored in DOBBOX.. which will ultimately be age in years.

Please tell me I've worked this out right?

Thanks

[fumei]

"so i'm learning as I go and working out vb from examples using logic."

Bravo! That is the best way to learn.

Your code for actioning the bookmark is not quite correct. Yes, it would put the CalcAge result of DOBTEXT at the bookmark "agebook".

However....in doing so, it deletes the bookmark. Yes, you are attempting to re-create the bookmark, but....
ActiveDocument.Bookmarks.Add _
Name:="agebook", _
Range:=bmRange
should fail because you are using bmrange...the range of an object that no longer exists. The correct code is:
Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
With r
.Text = strText
.Collapse 0
.MoveStart Unit:=wdCharacter, _
Count:=-Len(strText)
End With
ActiveDocument.Bookmarks.Add _
Name:=strBM, Range:=r
End Sub


I use a Sub in a global template as filling in bookmarks is a very common thing to do, and I want to be able to use it whenever I want/need.

Basically, the Sub accepts a bookmark name (strBM) and text to go into that bookmark (strText).

It creates a range of the bookmark, and inserts the text.
It collapses the range to its end (Collapse 0), moves the Start backwards the length of the input string -Len(strText) - notice the minus sign before Len.

It then uses THAT range - essentially the input text - to re-create the bookmark with the same name as original input.

Thus...the bookmark is filled with the input text. This can be used over and over, the bookmark will adjust to the content given the Sub.

All relevant code in the attached demo. Click "Show The Form" on the top toolbar. This is simplified with one textbox (txtDOBText), and one commandbutton (cmdDone).

The commandbutton Calls the FillBM sub-routine (passing parameters) and then unloads the userform.

Note that you do NOT have to do a two step process, like:

1. get the value of CalcAge
2. pass that value to the Sub FillBM

You can pass everything as ONE instruction. Here is the entire code for the commandbutton.
Private Sub cmdDone_Click()
Call FillBM("agebook", CalcAge(txtDOBText.Text))
Unload Me
End Sub
Because the function CalcAge is declared as returning a string:

Function CalcAge(birthdate As Date) As String

and the Sub requires strings:

Sub FillBM(strBM As String, strText As String)

then: Call FillBM("agebook", CalcAge(txtDOBText.Text))

is legitimate, as CalcAge(txtDOBText.Text) IS a string.

Final note: I did not change the returned string from "Your age is X years, Y months, Z days"

Adjust as required.

[davidboutche]

It took a couple of read throughs but is now starting to make send. I've not had the chance to try it yet, but where you call from the form FillBM, could you replace the CalcAge function with other items such as textboxs? Hence being able to use the FillBM lots of times with just quick and easy variables?

Also, what does

unloadme
mean?

Thanks for all the help... a steep but beneficial learning curve for me.:thumb

[davidboutche]

Just tried the FillBM function with other data and it works!! That's going to save me loads of needless code in future by just calling the one function each time.

unloadme?

[fumei]

You said you were using a userform. Userforms should always be properly unloaded when you are finished with them. Thus...Unload Me, with "Me" (as the instruction is in the userform code module) meaning the userform itself.

"That's going to save me loads of needless code in future by just calling the one function each time."

That is precisely the purpose of functions.