Create VBA Function to Calculate Age in Excel or Access | Kovolff

When you have a list of birth dates in Excel, it would be useful to have a function able to quickly calculate the age of each of these people or entities

This can be easily done by building an age calculating function in Excel VBA

Step 1: Save your Excel as a Macro Enabled file. It gets the XLSM Ending

Step 2: Go to the Developer Menu and then to the menu Insert to insert a new Module

Step 3 (optional): Rename your Module from the generic module1 to any name you fancy

Step 4: Create function shell

Public Function age_calc(birth_date As Date, Optional result_index As Integer = 0) As Variant

' outputs
Dim output_array(25) As Variant
output_array(0) = “test”

age_calc = output_array(result_index)

End Function

This function takes birth_date, which is of type date and a second optional parameter called result_index.

It outputs an array of values (hence as Variant at the top). In the shell above it just outputs one value, which is the text test

Remember in VBA the function must always return the name. Therefore the last line
age_calc points to the output array, otherwise the function won’t return anything.

Step 5: Parse today’s date into the day, month and year of today
In VBA you get today’s date with the function Date()

To get today’s day you call set a variable equal to Day(Date),
For today’s month you set your variable to Month(Date) and for today’s year: Year(Date)

Step 6: Parse birth date
This is done in similar fashion as with today’s date

Step 7: Use if conditions to calculate age
The logic is as follows
If birth month is smaller than today’s month then age = today’s year - birth year
Else if birth moth = today’s month then
If birth month smaller or equal than today’s day then age = today’s year - birth year
Else age = today’s year - birth year - 1
Else
age = today’s year - birth year - 1

Translating that in English
Should your birth month be in the past then you’ve gotten older hence today’s year - birth year

Should your birth month be right now then we have to check your day of birth
If it is today or any previous day this month, then you’ve gotten older
Therefore your age = today’s year - birth year

Otherwise you have not gotten older yet and thus your age = today’s year - birth year -1

By having the function output an array, you as a developer or user can also output the intermediate value for checking or to use them in further calculations.

You can download the Excel file from GitHub:

#visualbasic #excel #age #access

[tutsecret]

How I can have this on Access. My table has DOB and Age field. Users are inputing the age manually looking at a hardcopy age chart. To me it's stupid and cumbersome and slow down the data input. So I set up the query and I got the Age on column called AutomateAge. But how I can make this Age visible inside the table Age field. The only way I can do is copy the whole AutomatedAge column and paste on the Age field that is also inside the query, doing this way, the Age field inside the table gets visible all the age value. But is there a another way without copying and paste. I don't want the users go to the query to see the automated Age to copy and paste. The users just use the table to input data and view them. It would be nice when user is entering the DOB, the next column the Age would be populated automaticaly. Thank you.