Writing A Custom Function

In this blog post we will create a custom function that returns someone’s age based upon their date of birth.

• 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:

Let’s try with another known date, your own age (ages will vary):

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 Posts