In this blog post we will create a custom function that returns someone’s age based upon their date of birth.
To write a custom function we need to consider the following:
- A returned value is needed, so we must use a function.
- The value returned will be somebody’s age, so we should return an Integer.
- The function needs to know the student’s DOB, so a Date parameter is needed.
- We also need a relevant function name; let’s call it calculateAge.
The signature of the function is then:
Function calculateAge(DOB As Date) As Integer End Function
We need a variable to store the age and to store today’s date:
Dim iAge as Integer Dim dToday as Date
Now we need to know the difference between DOB and today’s date in years. VBA has a function for that, DateDiff. Let’s set dToday to today’s date and use DateDiff to give us the age in years.
dToday = Date() iAge = DateDiff(“yyyy”, DOB, dToday) ' yyyy interval date
Finally, we also need to return iAge to the calling method by doing the following:
calculateAge = iAge
The whole function now looks like this:
Function calculateAge(DOB As Date) As Integer Dim iAge As Integer Dim dToday As Date dToday = Date iAge = DateDiff("yyyy", DOB, dToday) ' yyyy interval date calculateAge = iAge End Function
In the immediate window we call the function with a date for the argument:
|Print calculateAge (#19/12/1997#)
Let’s try with another known date, your own age (ages will vary):
|? calculateAge (#15/11/1978#)
Note: The Date function returns the current date (as defined by your operating system) so the results you get from the following example will be different from the results we obtained.
Custom functions are a vitally important part of VBA. In order to write the code you want, you will need to master them and the way in which they are constructed.
Related PostsBuilt-In Functions